LIKE clause is not working in SQL server

Question

I have a table which have the following columns in SQL Server 2005:

DocID   NSN  QTY
-----------------
DHA12    32    5
DSRB23   22   45
TF22     70   23

Whenever I perform the following query to get all the NSN which have a DocId that starts with either DSRB or DHA, it gives me empty set:

SELECT  DocId, NSN, Qty, RequestDate, ReceiveDate
  FROM  Orders 
 WHERE (DocID LIKE '%DSRB%') 
   AND (DocID LIKE '%DHA%')  

I guess the problem with having two LIKE clauses because when I delete the last LIKE clause, the query works fine.

Solution 1

You get an empty result because there is no row in your table where DocID is like both %DSRB% and %DHA% at the same time. You don't say in your question, but I guess you are expecting to receive the two rows with DocIds DHA12 and DSRB23.

To do this, you need to select rows where DocID is like either %DSRB% or %DHA%. Try changing the AND in your WHERE clause to an OR:

SELECT DocId, NSN, Qty, RequestDate, ReceiveDate
FROM Orders
WHERE (DocID LIKE '%DSRB%') OR (DocID LIKE '%DHA%')

See this introduction to SQL Logical Operators if you want more examples.

Solution 2

If you want to find rows that contain either XX or YY, you need to use OR:

SELECT DocId, NSN, Qty, RequestDate, ReceiveDate 
FROM Orders 
WHERE (DocID LIKE '%DSRB%')
   OR (DocID LIKE '%DHA%')

Solution 3

Don't you want an OR in there instead of an AND?

Solution 4

Make it "OR" instead of "AND". At this point, you're saying both clauses in the "WHERE" have to be true.

Solution 5

Are you sure you need AND? You have no records shown that meet those requirements. I think you may want OR, which should return two records.

View more solutions

Comments

[marc_s]

IF you want to search for something that starts with a string of char, you should use LIKE 'DHA%' - in that case, SQL Server has a chance of using an index that might be present, to speed things up. If you use the expression you have now (LIKE '%DHA%'), you search for something that contains those three letters anywhere in the string, and SQL Server has no chance at all to use any index - even if it were present.