4

It does not work in MySQL (8.0.5+) using ICU-REGEXP to perform a search on the word boundary. As far as I understand it should be a-la

$ mysql -e 'SELECT REGEXP_LIKE("aaa abc ccc", ".*\b+abc\b+.*")'
+---------------------------------------------+
| REGEXP_LIKE("aaa abc ccc", ".*\b+abc\b+.*") |
+---------------------------------------------+
|                                           0 |
+---------------------------------------------+

but this option does not work.

Wiktor Stribiżew
  • 484,719
  • 26
  • 302
  • 397
chaturanga
  • 43
  • 5

1 Answers1

6

First, note that REGEXP_REPLACE can match strings partially, and you do not need .* before and after a search word.

The \ char should be escaped in order to define a literal backslash, since \ itself allows escaping characters for the MySQL engine. See this MySQL 8 documentation:

Note

Because MySQL uses the C escape syntax in strings (for example, \n to represent the newline character), you must double any \ that you use in your expr and pat arguments.

Thus, you need

REGEXP_LIKE("aaa abc ccc", "\\babc\\b")
Wiktor Stribiżew
  • 484,719
  • 26
  • 302
  • 397
  • 3
    It caught me by surprise that the previous `[[:<:>:]]` expression is not supported any more. Now it use ICU libraries but not all new allowed expressions are documented by MySQL. To complement this answer for people who write PHP, the double backslash need to escaped also something like this: `$sql="SELECT REGEXP_LIKE('AA AAA ABAA','\\\\bAA\\\\b')";`. In fact any other language will need these double escaped backslashes. – CarlosH. Jul 24 '18 at 02:57
  • 1
    Whether you need one, two, or four backslashes depends on the number of layers of interface you have to go through. The final syntax is one backslash. – Rick James May 02 '20 at 19:12