匹配 MySQL 中的 Regex 用于重复单词，条件排除括号 [英] Match Regex in MySQL for repeated word with condition exclude the parentheses

问题描述

我遇到了查询问题.我使用 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]+[[:>:]])*$'

SQLFiddle

正则表达式分解

正则表达式实际上使用了许多重复的部分，所以它有点长:

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 2s in the string.

这篇关于匹配 MySQL 中的 Regex 用于重复单词，条件排除括号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！