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.