How to do a case-insensitive LIKE in a case-sensitive database?

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%'

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.

StackExchange Database Administrators Q#209546, answer score: 30