CODEWITHSUNDEEP
mysqlsqlregex
muhnizar
muhnizar

Reputation: 327

Match Regex in MySQL for repeated word in one column

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 2 new 2 new      |
|   3   |   new 2                |
|   4   |   2 new 2new           |

The Result I expected

---------------------------------
|   ID  | Description           |
---------------------------------
|   2   |   new 2 new 2 new     |
|   4   |   2 new 2new          |

The Query I've tried so far:

SELECT * FROM a WHERE (description REGEXP '([^2][^0..9]])2( [^2][^0..9])([^2][^0..9]])2( [^2][^0..9])')

http://sqlfiddle.com/#!2/7d712/2

Could anyone help me to solve this :(?

Upvotes: 0

Views: 100

Answers (2)

hwnd
hwnd

Reputation: 70722

As @Tim Pietzcker pointed out, your regular expression does not do what you may think it does. If I understand correctly, I believe you are looking for the following regular expression. This returns ID 2 and 4 respectively.

^[^2]*2[^2]*2[^2]*$

Your SQL query would be:

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

SQL Fiddle

Upvotes: 0

Tim Pietzcker
Tim Pietzcker

Reputation: 336128

Your regex isn't doing what you think it does (although I can't quite guess what you think it does...)

A translation of part of your regex:

([^2][^0..9]])2

means:

(         # Start a group
 [^2]     # Match one character except "2"
 [^0..9]  # Match one character except "0", "." or "9"
 ]        # Match "]"
)         # End of group
2         # Match "2"

Upvotes: 1

Related Questions