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.

Answer 1

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