Query won't give me correct results with LIKE clause

Databases
1

I have the following SQL query in MS SQL Server 2012:

SELECT TOP (1000) [id]
      ,[name]
      ,[description]
      ,[comment]
      ,[locationId]
      ,[capacity]
      ,[isFull]
      ,[entryDate]
      ,[endDate]
  FROM [mydatabase].[dbo].[ContentLocation]
  WHERE name  LIKE 'BOX%' OR name  LIKE 'BIS%'
  AND isFull = 0

Problem is that it is still giving me results where isFull = 1.

When I run just the following query, it will give me only those results where isFull = 0. isFull is a true or false column with bit value of 0 or 1.

SELECT TOP (1000) [id]
      ,[name]
      ,[description]
      ,[comment]
      ,[locationId]
      ,[capacity]
      ,[isFull]
      ,[entryDate]
      ,[endDate]
  FROM [mydatabase].[dbo].[ContentLocation]
  WHERE isFull = 0

What’s the issue with my first query when I want it to be filtered using LIKE and it won’t give me correct results?

2

Because AND has a higher precedence than OR, your existing logic evaluates as -

WHERE name LIKE 'BOX%' OR
 (name LIKE 'BIS%' AND isFull = 0)

What you need to do is use () to force the logic to OR the two name terms, then AND that result with the isFull term.

WHERE (name  LIKE 'BOX%' OR name  LIKE 'BIS%')
  AND isFull = 0
3 Likes
3

Thank you for explaining it nicely. One question - does it matter how many name LIKE 'BOX%' OR name LIKE 'BIS%' LIKE clause I have in my SQL query? I have around 30 items where I want to use LIKE clause and it could increase or decrease in future.

4

There’s a maximum packet size setting on the database server that will limit the number of characters in the sql query statement. I don’t know what it is called or what its default value is for SQL server.

However, what you are showing us, with hard-coded values, indicates you are doing something in the hardest way possible. How are these values being selected and where are they coming from? Perhaps you should be doing exact matches by selecting from existing values and using an IN() comparison in the query? If these values are being submitted to your web page, you should be using a prepared query.

5

to expand on this idea, the two values you’ve shown, @Jack_Tauson_Sr, are specific to 3 characters – does that hold true for the other 30 values?

because then you can change this –

WHERE name LIKE 'BOX%'
   OR name LIKE 'BIS%'
   OR name LIKE 'FOO%'
   OR name LIKE 'BAR%'
    ...

to this –

WHERE LEFT(name,3) 
   IN ('BOX','BIS','FOO','BAR' ... )
6

Yes, the other 30 values will be unique/specific if that’s what you are asking. But it’s not necessarily going to be of three characters always.

So I believe it won’t work then?

7

correct

8

I think what I should have asked is the following:

does it matter how many name LIKE 'BOX%' OR name LIKE 'BIS%' LIKE clause I have in my SQL query when I'm using this query from a java web service (for example) i.e. using JDBC or Spring? I'm not worried about the limit of characters in the sql query statement on SSMS client.

9

The issue with your first query is that the AND operator has a higher precedence than the OR operator, which means that the condition is evaluated as:

WHERE name LIKE ‘BOX%’ OR (name LIKE ‘BIS%’ AND isFull = 0)

This will return all the rows where name starts with ‘BOX’, regardless of the value of isFull, and only the rows where name starts with ‘BIS’ and isFull is 0.

To get the desired result, you need to use parentheses to group the OR conditions together, like this:

WHERE (name LIKE ‘BOX%’ OR name LIKE ‘BIS%’) AND isFull = 0

This will return only the rows where name starts with either ‘BOX’ or ‘BIS’ and isFull is 0.

10

Isn’t that almost exactly what @mabismad posted last week?

1 Like
11

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.