Error Code: 3685. Illegal argument to a regular expression

Question

I am trying to find an exact number in MySQL 8.0 using the below SQL statement

SELECT * FROM rulebook.node__body 
WHERE body_value REGEXP "[[:<:]]DVP[[:>:]]";

when i am running the above SQL statement i am getting below error

Error Code: 3685. Illegal argument to a regular expression

could you please anyone tell me where i am making mistake.

Why are you using `REGEXP` on what appears to be numeric data? — Tim Biegeleisen, Jan 31 '20 at 05:45
@TimBiegeleisen i have given an example actually i want to look for a word — Zahid Hussain, Jan 31 '20 at 05:50
@TimBiegeleisen i have changed my SQL statement and table could you please have a look — Zahid Hussain, Jan 31 '20 at 05:55
@ZahidHussain: what are you trying to look for? Anything containing "<" and DVP and ">" ? — Dio Phung, Jan 31 '20 at 06:04

Leo Galleguillos · Answer 1 · 2020-05-21T01:10:37.457

43

This question will probably become more popular as adoption of MySQL 8.0 increases and previously-stored SQL queries using REGEXP start to break.

According to MySQL 8.0 Reference Manual / ... / Regular Expressions, "MySQL implements regular expression support using International Components for Unicode (ICU)."

According to MySQL 5.6 Reference Manual / ... / Regular Expressions, "MySQL uses Henry Spencer's implementation of regular expressions."

Therefore, since you are using MySQL 8.0, rather than using [[:<:]] and [[:>:]], you can now use \b. Your query might look like this:

SELECT *
  FROM `rulebook`.`node__body`
 WHERE `body_value` REGEXP "\\bDVP\\b"
     ;

edited May 21 '20 at 01:10

answered Mar 28 '20 at 20:06

Leo Galleguillos

2,429
3
25
43

9

This worked for me, however using it in php I had to escape each of the two backslashes ending up with '\\\\bDVP\\\\b' – SuprMan May 20 '20 at 00:50
1

@SuprMan, Thanks, I was trying with double and didn't worked, but with four backslashes, it worked. – amir22 May 05 '21 at 11:01
hm... it works for me with 2 backslashes, @amir22 maybe because of MySQL version? 5.7 for me. – Alex Strizhak Sep 14 '21 at 17:53

score 2 · Answer 2 · answered Oct 25 '21 at 14:58

In php() replace special symbol as four "backslashes + b"

Example:

$where = "regexp '[[:<:]](" . $custom_category . ")[[:>:]]'";

Replace with:

$where = "regexp '\\\\b(" . $custom_category . ")\\\\b'";

Parentheses don't matter...

If you wont replace recursive in your sites, use this script, for search and replace in all php files this pattern:

# find /var/www/user/data/www/site.com/ -type f -name '*.php' | xargs sed -i  's/\[\[\:[<|>]\:\]\]/\\\\\\\\b/g'

score -2 · Answer 3 · answered Jan 31 '20 at 06:10

-2

Your regex contains an invalid bracket. With the assumption that you are trying to find any row where body_value contains < and DVP and >, you can try:

SELECT * FROM rulebook.node__body 
WHERE body_value REGEXP '.*<.*DVP.*>.*';

answered Jan 31 '20 at 06:10

Dio Phung

5,944
5
37
55

2

`[[:<:]]` and `[[:>:]]` are word boundaries in MySQL REGEXP. They are not intended to be literal characters in the column being searched on (as proposed in your solution). – Leo Galleguillos Mar 19 '20 at 19:18

Error Code: 3685. Illegal argument to a regular expression

3 Answers3

Linked