mySQL like, but not like

Question

I need help with one mysql query. I am unable to find solution for specific like query. I am not even sure, if LIKE operator is what I am looking for.

What I am trying to achive is, finding all rows containig url, if that url is not foo.com

I want this rows:

Lorem ipsum dolor sit amet example.com consectetuer vitae ut a lobortis metus.

Lorem ipsum dolor example.com sit amet consectetuer foo.com vitae ut a lobortis metus.

But I dont want this rows:

Lorem ipsum dolor sit amet consectetuer foo.com vitae ut a lobortis metus.

Lorem ipsum dolor sit amet consectetuer vitae ut a lobortis metus.

Queries like this wont work..

WHERE str like '%.com%'

WHERE (str LIKE '%.com%') && (str NOT LIKE '%foo.com%')

Any help would be appreciated.

Answer 1

Replace 'foo.com` to space then like '%.com%'.

REPLACE(str , 'foo.com', ' ') like '%.com%'