How to write regex lookahead / lookbehind in mysql

Question

I'm trying to do something like

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

and i got an error

Got error 'repetition-operator operand invalid' from regexp

due to '?' → see # 1139 - Got the error "operand of the repeat operator is 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 REGEXPdoes not support prediction, 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'