Lay András Lay András - 5 months ago 18
SQL Question

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?

Answer

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) %'
Comments