Stephen Stephen - 1 year ago 59
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.

Answer Source

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 '[[:>:]]($|[^-])'.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download