CODEWITHSUNDEEP
mysqlregex
Lay András
Lay András

Reputation: 855

Mysql match words with parentheses boundaries

I have this table:

mysql> SELECT * FROM test;
+----+---------------+
| id | duma          |
+----+---------------+
|  1 | bbb ccc ddd   |
|  2 | bbb (ccc) ddd |
|  3 | ccc ddd eee   |
|  4 | (ccc) ddd eee |
|  5 | aaa bbb ccc   |
|  6 | aaa bbb (ccc) |
|  7 | bbb(ccc)ddd   |
|  8 | (ccc)dddeee   |
|  9 | aaabbb(ccc)   |
+----+---------------+
9 rows in set (0.00 sec)

I'd like to match "(ccc)" string as words only, so i'd like to see rows #2, #4 and #6 in the result. I tried theese:

mysql> SELECT * FROM test WHERE duma REGEXP '[[:<:]](ccc)[[:>:]]';
+----+---------------+
| id | duma          |
+----+---------------+
|  1 | bbb ccc ddd   |
|  2 | bbb (ccc) ddd |
|  3 | ccc ddd eee   |
|  4 | (ccc) ddd eee |
|  5 | aaa bbb ccc   |
|  6 | aaa bbb (ccc) |
|  7 | bbb(ccc)ddd   |
|  8 | (ccc)dddeee   |
|  9 | aaabbb(ccc)   |
+----+---------------+
9 rows in set (0.00 sec)

Ok, the parentheses is pattern characters, i tried to escape it:

mysql> SELECT * FROM test WHERE duma REGEXP '[[:<:]]\\(ccc\\)[[:>:]]';
Empty set (0.00 sec)

mysql> SELECT * FROM test WHERE duma REGEXP '[[:<:]][(]ccc[)][[:>:]]';
Empty set (0.00 sec)

Is there any solution?

Upvotes: 1

Views: 754

Answers (5)

Rick James
Rick James

Reputation: 142518

REGEXP '(^| )[(].*[)]( |$)'

This is probably faster than the OR of 3 LIKEs.

Your definition of "word" is to have a space or beginning/end of string on either side. That is not the conventional definition, so all the answers given so far (including mine) are not correct with the conventional definition.

Upvotes: 0

Fredster
Fredster

Reputation: 776

You can use like with or to cover the spaces possibilites you want ...

 SELECT * FROM test 
 WHERE duma LIKE '(ccc) %' 
 OR duma LIKE '% (ccc)' 
 OR duma LIKE '% (ccc) %'

Upvotes: 1

&#193;lvaro Gonz&#225;lez
&#193;lvaro Gonz&#225;lez

Reputation: 146573

Escape madness apart, a parenthesis is not a word character so the word boundary marker won't match. From the manual:

[[:<:]], [[:>:]]

These markers stand for word boundaries. They match the beginning and end of words, respectively. A word is a sequence of word characters that is not preceded by or followed by word characters. A word character is an alphanumeric character in the alnum class or an underscore (_).

Upvotes: 2

sagi
sagi

Reputation: 40491

You don't need REGEXP here, you can simply use LIKE for this comparison: 

SELECT * FROM test
WHERE duma LIKE '%(ccc)%';

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133400

For this kind of word search you can use like 

 SELECT * FROM test WHERE duma like '%(ccc)%';

Upvotes: 2

Related Questions