SQL - exclude all items containing 'foo' except those with 'foo bar'-CodePudding

I'm trying to run a query in mysql that will exclude all items containing the wording 'foo xxx' or just 'foo', but including the specific entry 'foo bar'.

How to do this?

CodePudding user response：

One possible way:

SELECT  *
FROM test
WHERE col1 regexp  '(^|[[:space:]])foo([[:space:]]|$)'
AND  col1 regexp  '(^|[[:space:]])bar([[:space:]]|$)';

Above query will return only the rows in which both foo and bar are present and separate it doesn't matter the order

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=0555bcc45ba479489b0367a0b08ae858

A good article on REGEXP:https://www.oreilly.com/library/view/mysql-cookbook/0596001452/ch04s08.html

Have a look on MySQL Full text search