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][1] SELECT Seminar classes. You can find additional collation information though from [Ben Snaidero   from MS SQL Tips.][2]

[MSDN on Collate.][3]


  [1]: https://sqlworkbooks.com/
  [2]: https://www.mssqltips.com/sqlservertip/3215/how-column-collation-can-affect-sql-server-query-performance/
  [3]: https://learn.microsoft.com/en-us/sql/t-sql/statements/collations?view=sql-server-2017