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.