Case sensitive search in SQL Server

I cannot find a real necessity to do a case sensitive search in the database. And if it was needed the column or the database should have had the proper collation.

But beyond all these you want to do a case sensitive search on a column, then you can do it the following ways.

If you need an exact match then you may want to use this.

declare @searchChar varchar(10)
set @searchChar = 'SoMeJUnk'
select col1 from tbl1
where cast(col1 as varbinary) = cast(@searchChar as varbinary)
and col1 = @searchChar


Well, adding the expression col1 = @searchChar in the above query might seem redundant. But it will hit the index if there is one and will definitely perform better than without that condition. Thanks to Tibor Karaszi, the redundant expression was his brainchild.

If you need to have a pattern matching too, use this

declare @searchChar varchar(10)
set @searchChar = 'SoMeJUnk'
select col1 from tbl1
where col1 COLLATE SQL_Latin1_General_CP1_CS_AS like '%' + @searchChar +'%'

I don't remember who came up with the second approach.. Its not mine though :)