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)'
Answer
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