1

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 '^'

patrick
  • 10,664
  • 7
  • 58
  • 75
  • have a look at this question : http://stackoverflow.com/questions/23589174/match-a-pattern-except-in-three-situations-s1-s2-s3 – Aserre May 19 '14 at 12:24
  • Why not use `LIKE` and `NOT LIKE` ? – Marius.C May 19 '14 at 12:25
  • @Marius.C, it can be done with two REGEXP's as well (SELECT * from DB where geo regexp '[[:<:>:]]' and geo NOT REGEXP "!", but that's not as time-efficient as creating a single regexp, 'contains word, but no exclamation point' – patrick May 19 '14 at 12:41

1 Answers1

2

You can use this regex:

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

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

anubhava
  • 664,788
  • 59
  • 469
  • 547