How to write a regex lookahead/lookbehind in mysql

Question

I'm trying to do something like

SELECT * FROM table WHERE column REGEXP (abc)(?=def)

and I got the error

Got error 'repetition-operator operand invalid' from regexp

due to the '?' -> see #1139 - Got error 'repetition-operator operand invalid' from regexp

Is there an equivalent in mysql that I don't see in https://dev.mysql.com/doc/refman/5.7/en/regexp.html ?

or maybe another mysql function that I don't know yet?

Answer 1

MySQL REGEXP does not support lookaheads, but you can try to achieve the same logic using something like this:

WHERE column LIKE 'abc%' AND
      SUBSTRING(column, INSTR(column, 'abc') + 3, 3) <> 'def'