Monday, July 16, 2007

Forcing SQL Server to be Case Sensitive in Query Results

Is there a way to force SQL SERVER to be case sensitive in query results?


Yes, Microsoft has an excellent knowledgebase entry concerning this very issue. The knowledgebase article is Q171299.

In summary, there are two solutions to making the query results case sensitive - one concerns the configuration of the server during its installation and the other entails a coding technique.


1. During installation of the server you can select the character set and sort order. The default sort order is case- insensitive. This implies that case is not factored into the value of a character making. 'A'='a',


2. You can obtain case sensitive results by coding your query using the CONVERT function. This " relies on the fact that the actual data is stored in binary form, with each character represented by a unique hexadecimal value. You can use the CONVERT function to convert the data from character format to a string of hexadecimal values." For example, if you wanted to select companies whose name is 'Acl' and only 'Acl' (i.e. Upper first character, and lower case remaining two characters) the following will produce the desired results: SELECT * FROM table1 WHERE CONVERT(binary(3),company)=CONVERT(binary(3),'Acl')


  Anugrah Atreya