Zelong Zelong - 5 months ago 48
SQL Question

How to escape asterisk (*) in MySQL in a REGEXP

I tried matching keywords with

REGEXP
in MySQL as following:

-- Match "fitt*", the asterisk "*" is expected to be matched as-is

> select 'aaaa fitt* bbb' regexp '[[:<:]]fitt\*[[:>:]]'; -- return 1, ok
> select 'aaaa fitttttt* bbb' regexp '[[:<:]]fitt\*[[:>:]]'; -- return 1 as well, but should return 0

> select 'aaaa fitt* bbb' regexp '[[:<:]]fitt\\*[[:>:]]'; -- return 0, failed


How to escape the asterisk (
*
) in order to exactly match the character
*
?

Answer

\\* is the correct way to match the asterisk. But [[:>:]] won't match after it, because that only matches between a word character and a non-word character, and * is not a word character. Instead, you need to match a non-word character there explicitly. You also need an alternative for the end of line, since that's the other type of word boundary.

> select 'aaaa fitt* bbb' regexp '[[:<:]]fitt\\*([^[:alnum:]]|$)'; -- returns 1
> select 'aaaa fitttttt* bbb' regexp '[[:<:]]fitt\\*([^[:alnum:]]|$)'; -- returns 0

Another way to match the asterisk explicitly is by putting it in a character class.

> select 'aaaa fitt* bbb' regexp '[[:<:]]fitt[*]([^[:alnum:]]|$)'; -- returns 1
> select 'aaaa fitttttt* bbb' regexp '[[:<:]]fitt[*]([^[:alnum:]]|$)'; -- returns 0