Little SQL Server Tricks: Case-Sensitive String Comparison

Running your database with a case-insensitive collation is often a great help. If you search for 'de' you find 'de', 'De', 'dE' and 'DE' as well. However, if you need to make a case-sensitive search, that benefit is now a liability and all the other cases clutter your result.

Luckily for us, you can make a small change to your SELECT statement and switch to a case-sensitive search. All you need to do is to add a COLLATE Latin1_General_CS_AS (or any other collation with a CS) in your WHERE clause:

1
2
3
4
SELECT [BusinessEntityID]
      ,[Name]
FROM [Sales].[Store]
WHERE Name like '%inc%' COLLATE Latin1_General_CS_AS

This query will give you all rows that have the letters inc in lower case somewhere in the name but ignores Inc or any other combination that includes capital letters. Use this little trick when you need a case-sensitive search.