Stephen Stephen - 3 months ago 7x
SQL Question

REGEXP to NOT match if there is a hyphen before a variable

I am having a hard time correctly matching a variable ($myvar) using MySQL REGEX.

My first thought was to match the word (\b), but this will match both "testtype" and "edit-testtype" in my example below, which I don't want:

SELECT "edit-testtype, themes, upload, plugin-editor" REGEXP '\\b$myvar\\b';

After a lot of internet searching I thought I had the answer with this:

SELECT "edit-testtype, themes, upload, plugin-editor" REGEXP '\^(?!-)\\b$myvar\\b';

but it will fail on any of the other words in the string. I guess what I need is a REGEX that will NOT match any $myvar that is preceded by a hyphen/dash.


If you want to try a regex way, you may use

REGEXP CONCAT('(^|[^-])[[:<:]]', $myvar', '[[:>:]]')

This won't work if your$myvar contains special chars like [, (, + - they would need to be escaped.

Note that (^|[^-]) matches the start of string or a non-hyphen, [[:<:]] matches a leading word boundary and [[:>:]] matches the trailing word boundary.

If the search term cannot be followed with a hyphen, turn [[:>:]] to '[[:>:]]($|[^-])'.