问题描述
我遇到了查询问题.我使用 mysql 作为数据库.我想使用 REGEX 来匹配我预期的结果,并且表格是
I'm having a query problem. I use mysql as DB. I want to use a REGEX to match the result I expected and The Table is
table A
----------------------------------
| ID | Description |
----------------------------------
| 1 | new 2 new 2 new 2 new |
| 2 | new 21 new 2 new |
| 3 | new 2th 2 (2/2) |
| 4 | 2new 2new (2/2) |
| 5 | new2 new 2new |
我期待的结果
- 数字 2 只能显示两次
- 2 之后/之前的字符必须是 varchar(空格之后除外)
- 特殊条件:任何带有(numeric/numeric)"模式的数字,如 ID=3 和 ID=4 都是可以接受的
The Result I expected
- numeric 2 can only show twice
- character after/before 2 must be varchar (except after whitespace)
- special condition : any numeric with pattern "(numeric/numeric)" like ID=3 and ID=4 is acceptable
| ID | Description |
---------------------------------
| 3 | new 2th 2 (2/2) |
| 4 | 2new 2new (2/2) |
| 5 | new2 new 2new |
到目前为止我尝试过的查询
the query I've tried so far
http://sqlfiddle.com/#!2/a227b/2
推荐答案
我提出这个正则表达式:
I propose this regex:
^([^2]|[[:<:]][0-9]+/[0-9]+[[:>:]])*([[:<:]]|[a-z])2([[:>:]]|[a-z])([^2]|[[:<:]][0-9]+/[0-9]+[[:>:]])+([[:<:]]|[a-z])2([[:>:]]|[a-z])([^2]|[[:<:]][0-9]+/[0-9]+[[:>:]])*$
它有点长,但它提供了更大的灵活性,因为这些字符串也被认为是有效的":
It's a bit long, but it allows some more flexibility in that those strings are considered 'valid' as well:
(2/2) 2new 2new
2new (2/2) 2new (2/2)
在代码中
SELECT
*
FROM
A
WHERE
description REGEXP '^(([^2]+|[[:<:]][0-9]+/[0-9]+[[:>:]])*2([[:>:]]|[a-z])){2}([^2]+|[[:<:]][0-9]+/[0-9]+[[:>:]])*$'
正则表达式分解
正则表达式实际上使用了许多重复的部分,所以它有点长:
The regex actually uses many repeating parts, so that's why it's a bit long:
^ # Beginning of string
( # Open repeat group
([^2]+|[[:<:]][0-9]+/[0-9]+[[:>:]])* # Any characters. See #1
2 # 2
([[:>:]]|[a-z]) # Word boundary or alphabet/letter. See #2
){2} # Close repeat group and repeat 2 times
([^2]+|[[:<:]][0-9]+/[0-9]+[[:>:]])* # Any characters. See #1
$
详细分类
#1
( # Open group
[^2]+ # Any characters except 2
| # OR
[[:<:]] # Open word boundary
[0-9]+ # Any numbers
/ # Forward slash
[0-9]+ # Any numbers
[[:>:]] # Close word boundary
)* # Close group and repeat any number of times
#2
( # Open group
[[:>:]] # Word boundary
| # Or
[a-z] # Letter/alphabet
) # Close group
单词边界匹配单词的开头和结尾.这里的单词定义是一系列字母、数字和下划线字符.
A word boundary matches the beginning and end of words. The definition of a word here is a series of alphabet, numbers and underscore characters.
[[:<:]]
是一个开放的词边界,因此匹配在一个词的开头.
[[:<:]]
is an opening word boundary and thus matches at the start of a word.
[[:>:]]
是一个开放的词边界,因此匹配在一个词的末尾.
[[:>:]]
is an opening word boundary and thus matches at the end of a word.
它们在此处的使用可确保 2
(以及数字/数字部分)不被其他数字包围(例如,因此使 21
失败)或计算 >2
如果你有例如 21/4
作为一个计数在字符串中的两个 2
s.
Their use here ensures that 2
(and the numeric/numeric parts) are not surrounded by other numbers (hence makes 21
fail for example) or count a 2
if you have for example 21/4
as one which counts towards the two 2
s in the string.
这篇关于匹配 MySQL 中的 Regex 用于重复单词,条件排除括号的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!