CODEWITHSUNDEEP
mysqlsqlregex
muhnizar
muhnizar

Reputation: 327

Match Regex in MySQL for repeated word

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 12th 2           |
|   4   |   2new 2new            |
|   5   |   new2 new 2new        |

The Result I expected
- numeric 2 can only show twice
- character after/before 2 must be varchar (except after whitespace) 

Table B
    ---------------------------------
    |   ID  | Description           |
    ---------------------------------
    |   4   |   2new 2new            |
    |   5   |   new2 new 2new        |

The Query I've got so far:

SELECT * FROM a WHERE 
(description REGEXP '^[^2]*2[^2]*2[^2]*$')

click here for sqlfiddle demo

could anyone help me to solve this?

Upvotes: 1

Views: 261

Answers (1)

Avinash Raj
Avinash Raj

Reputation: 174696

Use the below regex to get the Description of fourth and fifth ID's.

SELECT * FROM a WHERE 
(description REGEXP '^2[^2]*2[^2]*|\w+2[^2]*2[^2]*$')

http://sqlfiddle.com/#!2/1284e/18

Explanation:

  • Divide the above regex into two like 2[^2]*2[^2]* as one part and \w+2[^2]*2[^2]* as another part. In regex ^ represents the starting point and $ represents the end point.

  • 2[^2]*2[^2]*

    • 2 Matches the number 2.
    • [^2]* Matches any character not of 2 zero or more times.
    • 2 Matches the number 2.
    • [^2]* Matches any character not of 2 zero or more times.
    • This would get you the 4th ID.

  • | A logical OR operator usually used to combine two regexes which means match either this(before) or that(after).

  • \w+2[^2]*2[^2]*

    • \w+2 Matches one or more word characters which should be followed by the number 2. In your example, 5th ID satisfy this regex.
    • [^2]* Matches any character not of 2 zero or more times.
    • 2 Matches the number 2.
    • [^2]* Matches any character not of 2 zero or more times.
    • This would get you the 5th ID.

Upvotes: 3

Related Questions