Using negative lookahead Regex in MySQL

Question

I´m trying to write a regex to select all posts with just two dots in them (..) and discarding all posts that have three or more (..). I have come up with (?

but when i do SELECT * FROM table where column regexp '(?<!\.)\.{2}(?!\.)' i get an error. I seems that Mysql doesnt support negative lookahead. How can i fix this?

Use 2 conditions = first `like '%..%'` and second `not like '%...%'`. With proper quoting, of course. — Akina, Mar 19 '20 at 08:37

score 0 · Answer 1 · edited Mar 20 '20 at 03:28

You may use groups since all you need is a boolean result and the match is searched for only once:

SELECT * FROM table where column regexp '([^.]|^)[.]{2}([^.]|$)'

Details

([^.]|^) - any char but . or start of string
[.]{2} - two dots
([^.]|$) - any char but . or end of string

Note that any record containing this substring will be returned even if there are ... substrings, too, like abc...bc... To avoid those, you may just use

SELECT * FROM table where column     LIKE '%..%'
                      AND column NOT LIKE '%...%'

LIKE requires a full pattern match, so % matches any 0 or more chars from the start, .. or ... match the two or three dots, and then % matches the rest of the string.

Using negative lookahead Regex in MySQL

1 Answers1