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

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?

Ah, rats. I thought the duplicate target had an actual link to the official documentation explaining that those features don’t exist in mySQL. It links to the general Regex reference though, to which you also link. Sorry. Either way the bottom line seems to be that the features aren’t there — Pekka, Sep 27 '16 at 14:07
@Tim I’m not sure, I’ve never tried. Do you have an answer that differs from the one in the duplicate target? — Pekka, Sep 27 '16 at 14:09
@Pekka웃 Yes, I have a workaround and I was 5 seconds from posting when you closed. — Tim Biegeleisen, Sep 27 '16 at 14:10
MariaDB, a mySQL fork, seems to have this now https://mariadb.com/kb/en/mariadb/pcre/#positive-and-negative-look-ahead-and-look-behind-assertions — Pekka, Sep 27 '16 at 14:11

Tim Biegeleisen · Accepted Answer · 2019-01-25T15:12:35.680

2

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'

edited Jan 25 '19 at 15:12

answered Sep 27 '16 at 14:12

Tim Biegeleisen

Close. But fails to catch `"abcdef xyz abc"`. But I guess this is an ambiguous case. Does guillaume want this row or not? – Rick James Sep 27 '16 at 18:07
wouw, I didn't expect that my question will be answered so quickly. It took me some time to understand what you were trying to do here (didn't know the functions `SUBSTRING`, neither `INSTR`) and that's an interesting trick I couldn't possibly think about. Nevertheless I can't test it right now, I hope it will be used by someone else... – guillaume latour Oct 03 '16 at 16:20
had to do something similar today and remembered the question I asked :) well, it works ^^ – guillaume latour Aug 02 '17 at 09:13

1 Answers1