case sensitive string comparison

case sensitive string comparison

In SQL Server when you compare strings sometimes 'A' equals 'a' and sometimes 'A' does not equal 'a'. This depends on collation setting. Collation decides how strings are compared; you can have many different collations but the most important factor is if collation you have is case sensitive or case insensitive. If one of the strings or fields is case sensitive than 'A' is not the same as 'a' but if both are case insensitive then 'A' equal 'a'.

 

Below are several examples explaining case sensitivity strings comparisons and results.

 

select CASE WHEN 'a'='a' THEN 'match' else 'doesn''t match' end

 

The result of the above sql is always 'match'

 

select CASE WHEN 'A'='a' THEN 'match' else 'doesn''t match' end

 

The result of the above sql is 'match' if database collation set to case insensitive and 'doesn't match if collation is case sensitive.

 

select CASE WHEN 'A' collate sql_latin1_General_CP1_cs_as ='a' THEN 'match' else 'doesn''t match' end

 

If this case we force case sensitive collation using collate command and the results of the above query is always 'doesn't match'

 

select CASE WHEN lower('A') collate sql_latin1_General_CP1_cs_as = lower('a') THEN 'match' else 'doesn''t match' end

 

To compare two strings and ensure that are case insensitive I often use lower function on both sides. In the example above I enforced case sensitivity using collate command but I used lower on both sides hence made is case insensitive so sql result is 'match'