Case Sensitive comparison in SQL

No Gravatar

The default collation that Microsoft SQL uses (and requried collation for SCCM) is case-insensitive.  Here are some tips for performing a case-sensitive comparison despite the case-insensitve collation.

Convert data to binary

[cc lang=’sql’ line_numbers=’false’]SELECT * FROM MyTable
WHERE CAST(Value1 AS varbinary(255)) = CAST(Value1 AS varbinary(255))[/cc]

Using the COLLATE clause to dictate the case sensitiveness of the query

[cc lang=’sql’ line_numbers=’false’]SELECT * FROM MyTable
WHERE Value1 COLLATE SQL_Latin1_General_CP1_CS_AS = Value2 COLLATE SQL_Latin1_General_CP1_CS_AS[/cc]

Using BINARY_CHECKSUM function

[cc lang=’sql’ line_numbers=’false’]SELECT * FROM MyTable
WHERE BINARY_CHECKSUM(Value1) = BINARY_CHECKSUM(Value2)[/cc]

Thanks to Vyas for most of the info and some other idea: http://vyaskn.tripod.com/case_sensitive_search_in_sql_server.htm

February 15, 2013

Posted In: T-SQL