MySQL REGEXP word boundaries [[:<:]] [[:>:]] and double quotes

Question

I'm trying to match some whole-word-expressions with the MySQL REGEXP function. There is a problem, when there are double quotes involved.

The MySQL documentation says: "To use a literal instance of a special character in a regular expression, precede it by two backslash () characters."

But these queries all return 0:

SELECT '"word"' REGEXP '[[:<:]]"word"[[:>:]]';             -> 0
SELECT '"word"' REGEXP '[[:<:]]\"word\"[[:>:]]';           -> 0
SELECT '"word"' REGEXP '[[:<:]]\\"word\\"[[:>:]]';         -> 0
SELECT '"word"' REGEXP '[[:<:]] word [[:>:]]';             -> 0
SELECT '"word"' REGEXP '[[:<:]][[.".]]word[[.".]][[:>:]]'; -> 0

What else can I try to get a 1? Or is this impossible?

Answer 1

In MySQL up from 8.0.4 use: \\bword\\b

ref. https://dev.mysql.com/doc/refman/8.0/en/regexp.html#regexp-compatibility

Answer 2

Let me quote the documentation first:

[[:<:]], [[:>:]]

These markers stand for word boundaries. They match the beginning and end of words, respectively. A word is a sequence of word characters that is not preceded by or followed by word characters. A word character is an alphanumeric character in the alnum class or an underscore (_).

From the documentation we can see the reason behind your problem and it is not caused by escaping whatsoever. The problem is that you are trying to match the word boundary [[:<:]] right at the beginning of the string which won't work because a word boundary as you can see from the documentation separates a word character from a non-word character, but in your case the first character is a " which isn't a word character so there is no word boundary, the same goes for the last " and [[:>:]].

In order for this to work, you need to change your expression a bit to this one:

"[[:<:]]word[[:>:]]"
 ^^^^^^^    ^^^^^^^

Notice how the word boundary separates a non-word character " from a word character w in the beginning and a " from d at the end of the string.

EDIT: If you always want to use a word boundary at the start and end of the string without knowing if there will be an actual boundary then you might use the following expression:

([[:<:]]|^)"word"([[:>:]]|$)

This will either match a word boundary at the beginning or the start-of-string ^ and the same for the end of the word boundary or end-of-string. I really advise you to study the data you are trying to match and look for common patterns and don't use regular expressions if they are not the right tool for the job.

SQL Fiddle Demo