SQL Server LIKE – How to Perform Case-Insensitive Search in Case-Sensitive Database

collationlikesql server

My vendor requires the data warehouse database to be case sensitive, but I need to do case-insensitive queries against it.

In a case-sensitive database, how would you write this to be case-insensitive?

    Where Name like '%hospitalist%'

Best Answer

You can append a new collation to your select query to find case sensitive or insensitive. 

-- Case sensitive example
SELECT *
FROM TABLE 
WHERE Name collate SQL_Latin1_General_CP1_CS_AS like '%hospitalist%'

-- Case insensitive example
SELECT *
FROM TABLE 
WHERE Name collate SQL_Latin1_General_CP1_CI_AS like '%hospitalist%'

Just be aware of the performance problems this could present. You will need to scan the clustered index to adjust / find the values when you perform the collation. The way you are writing the LIKE piece also makes the query non-sargable.

I picked up the collation trick from Kendra Little's SELECT Seminar classes. You can find additional collation information though from Ben Snaidero from MS SQL Tips.

MSDN on Collate.

Related Question