CODEWITHSUNDEEP
sqlt-sqlsql-server-2008
oshirowanen
oshirowanen

Reputation: 15965

SQL NOT LIKE - Not working

UPDATE 2:

In one of the rows, in the column closed_by, it contains a null. If I replace the null with text, the query starts working, but it will not work with null. So it seems null is the problem, but the query should return rows which have null too, as pqr does not equal null.

UPDATE 1:

I have also tried set @user = 'pqr', but it makes no difference. It still returns 0 rows, when it should be returning 1 row, as 1 of the rows does not contain pqr.

ORIGINAL QUESTION:

I am trying to return rows which do not contain the id provided:

declare @user varchar(3)
set     @user = 'pqr'

select * from table1 where not( closed_by like @user )

closed_by contains data like

abc,def,ghi,jkl,mno

But this gives me no errors and returns no data, it should be returning a single row as pqr is not in 1 row.

Not sure what I am doing wrong.

Upvotes: 0

Views: 3992

Answers (6)

Aaron Bertrand
Aaron Bertrand

Reputation: 280590

NULL is unknown and therefore it is unknown whether it is like your pattern or not. You can solve this easily by using:

DECLARE @user VARCHAR(5) = '%pqr%';    
SELECT ... WHERE COALESCE(closed_by, '') NOT LIKE @user;

And in fact to be more accurate you probably want:

DECLARE @user VARCHAR(7) = '%,pqr,%';    
SELECT ... WHERE COALESCE(',' + closed_by + ',', '') NOT LIKE @user;

This way, if you later have data like 'trv,pqrs,ltt' it won't return a false positive.

However, having said all of that, having you considered storing this in a more normalized fashion? Storing comma-separated lists can be very problematic and I can assue you this won't be the last challenge you face with dealing with data structured this way.

Upvotes: 2

MatBailie
MatBailie

Reputation: 86783

You need to use the % and _ wildcard characters when using LIKE. Without them you actually just have WHERE NOT (closed_by = @user).

Also, be careful of accidental matches. For example LIKE '%a%' would match your example record. For such cases, I tend to ensure that the comma delimitered lists also have commas at the start and end. Such as; ',abc,def,ghi,jkl,mno,' LIKE '%,ghi,%'

But, more over, you're using a relational database. You would be better off with each entry as it's own record in a normalised structure. Although this give 1:many relationships, rather than 1:1 relationships, you get the benefits of INDEXes and much more flexibility in your queries. (Your LIKE example can't use an index.)

REPLY TO UPDATE 2:

Be careful of how you assume NULL logic works.

  • The result of NULL LIKE '%pqr%' is NULL
  • The result of NOT (NULL) is NULL

You need to change your code to use WHERE NOT (ISNULL(closed_by, '') LIKE '%pqr%')

Upvotes: 1

StevieG
StevieG

Reputation: 8729

You need to include a wildcard character:

declare 
@user varchar(5) 
set @user = '%pqr%'  

select * 
from table1 
where isnull(closed_by, '') not like @user

Upvotes: 1

hspain
hspain

Reputation: 17568

It sounds to me that you really are looking for equivalence, and not wildcard matches. Try this:

select * from table1 where closed_by <> @user

Upvotes: 0

Randy Minder
Randy Minder

Reputation: 48522

Try this:

select * from table1 where closed_by not like @user

And you may need to add the appropriate '%' characters to tell SQL Server which portion of the value to search. For example 'pqr%'

Upvotes: 0

Justin
Justin

Reputation: 86789

You may want to check the syntax of the LIKE operator - it accetps a pattern and so you would need to use something like this instead:

declare @user varchar(5)
set @user = '%pqr%'

The '%' is a wildcard and matches any string of zeor or more chracters.

FYI - SQL Server won't be able to use indexes with a a LIKE pattern that starts with a wildcard and so you may find that your query performs badly with large data sets.

Upvotes: 3

Related Questions