CODEWITHSUNDEEP
phpmysqlregex
user5250856
user5250856

Reputation:

MYSQL Regex Negative Lookahead alternative

I'm doing a query on a postcode/zip code field.
I've been doing a bit of research and negative lookaheads aren't supported:

"MySQL supports POSIX regular expressions, not PCRE"

Is there an alternative solution to the below, using the regular expressions supported by MYSQL?

(?i)^W(?!C) -- this is the solution in PHP

And an example query to the database

select postcode from `postcodes` WHERE LOWER(postcode) REGEXP '^W(?!C)'

Upvotes: 3

Views: 3442

Answers (2)

Julesezaar
Julesezaar

Reputation: 3406

You can inverse the regex of what you want to achieve and use NOT REGEXP

SELECT postcode from `postcodes`
WHERE postcode NOT REGEXP '^w(c|$)'

The piece of code above is about a single character as asked in the question. For people who end up here in this thread and look for a negative lookahead with a full word it is also possible in contrast with the other answer from @Wiktor Stribiżew

-- So not the word 'not_this_word' or end of the string '$'
SELECT postcode from `postcodes`
WHERE postcode NOT REGEXP '^w(not_this_word|$)'

OR you can go for a subquery with NOT IN

SELECT postcode from `postcodes`
WHERE postcode NOT IN (
    SELECT postcode from `postcodes` WHERE postcode REGEXP '^w(not_this_word|$)'
)

Upvotes: 2

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 627370

In MySQL, you may use

WHERE postcode REGEXP '^W([^C]|$)'

([^C]|$) matches any char but C or end of string. Also, no need to use TOLOWER as the regex search is case insensitive by default.

See the online tests:

SELECT 'wc' REGEXP '^W([^C]|$)'; // => 0
SELECT 'wR' REGEXP '^W([^C]|$)'; // => 1
SELECT 'w' REGEXP '^W([^C]|$)';  // => 1

Upvotes: 3

Related Questions