Cannot find a way to negate a regexp inside the pattern

Question

I can't find a way to use the regexp to find all matches that contains the first word and the second word but between the two should not be a specific word.

select '<p>66-155</p><p>en application</p>' regexp '66-155.*[<,p,>]en application'

should return 0

select '<p>66-155 en application</p>' regexp '66-155.*[<,p,>]en application'

should return 1

Answer 1

In MySQL versions before 8.0, you cannot use a single regex for that since it is a POSIX based regex that does not support lookarounds.

With earlier versions of MySQL, you could use something like

LIKE '%66-155%en application%' AND NOT LIKE '%66-155%<p>%en application%'

If, instead of literal substrings, you have regex patterns, then it would look like

REGEXP '66-155.*en application' AND NOT REGEXP '66-155.*<p>.*en application'

In MySQL 8.x, with the ICU regex engine, you may use a lookaround based regex:

REGEXP '66-155(?:(?!<p>).)*?en application'

The (?:(?!<p>).)*? is a tempered greedy token that matches any char (other than a line break char, to match any char including line breaks, add (?s) at the pattern start) with ., as few repetitions as possible (due to *? quantifier), that does not start a <p> char sequence.