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?
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.
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.