CODEWITHSUNDEEP
mysql
user5858
user5858

Reputation: 1221

Mysql not like and able to find a record

I've a query:

select * from table where license='92cbb46d087' and email='[email protected]' and comments NOT LIKE '%0da44455%';

If I remove comments onwards it returns me 1 record. Even if I change the NOT LIKE string to anything but it is not printing a record.

What I want is find any license matching a license and email and whose comments record does not contain specific payment id. Indeed '%0da44455%' or '%xxxyyxxysss%' or any random string does not exist in the table in comments column.

Upvotes: 0

Views: 66

Answers (2)

Ed Bangga
Ed Bangga

Reputation: 13006

Lets use subquery and select case to filter out those not like comments. 

select * from (select case when coalesce(comments, '') not like '%0da44455%' then 1 else 0 end as tagCom
    , * from table where license='92cbb46d087' and email='[email protected]') t1
where t1.tagCom = 0

Upvotes: 0

pensum
pensum

Reputation: 1019

If comments column can have NULL values, you won't get any output from NOT LIKE and LIKE conditions.

You need to write something like this instead : WHERE (ISNULL(comments,'')) NOT LIKE '%0da44455%'

The reason behind this is simply because these conditions don't know how to behave with null values — How do you compare null as something that doesn't exist with another thing that doesn't exist?

Upvotes: 2

Related Questions