CODEWITHSUNDEEP
mysqlregex
patrick
patrick

Reputation: 11711

MySQL REGEXP matching NO exclamation point and then a word

I have a problem putting together the right REGEXP in MySQL. I have a database that could have something like this:

id | geo
---+--------
1  | NL
2  | US NL
3  | !US
4  | US

these are entries for geo-targeting or geo-blocking. #3 is not US, #1 is NL only. If I want to look for everything for the US I am using:

SELECT * FROM db WHERE geo REGEXP '[[:<:]]US[[:>:]]'

This would return 2, 3 and 4, but I don't want 3. I tried this:

SELECT * FROM db WHERE geo REGEXP '^![[:<:]]US[[:>:]]'

But that looks for everything starting with an exclamation point. I'm looking for a REGEXP to have the word 'US' and NO exclamation point. I just can't figure out how to make a 'doesn't contain' instead of a 'starts with' since they're both done with '^'

Upvotes: 1

Views: 309

Answers (1)

anubhava
anubhava

Reputation: 785008

You can use this regex:

SELECT * FROM db WHERE geo REGEXP '(^|[^!])[[:<:]]US[[:>:]]';

This will match any non-word character except ! before US

Upvotes: 2

Related Questions