CODEWITHSUNDEEP
mysqlregexmysql-8.0
chaturanga
chaturanga

Reputation: 43

How to use REGEXP with escape sequences like word boundary?

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.

Upvotes: 4

Views: 1697

Answers (1)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626748

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")

Upvotes: 7

Related Questions