SQL Server: Case Sensitive Query

Someone at work sent this out to the team.
From LW:

Dear All, you might find this bit of code quite useful.  I was trying to find data within a table where the data was like lower case letters e.g a, b as opposed to A, B.  TSQL was not differentiating between lower and upper case so the following line forced it to do this substring(hradminblockcode,1,1) COLLATE SQL_Latin1_General_CP1_CS_AS

I used it in the following query

SELECT hrrefno FROM hrrepairmaster WHERE substring(hradminblockcode,1,1) COLLATE SQL_Latin1_General_CP1_CS_AS�
IN  (‘a’,’b’,’c’,’d’,’e’,’f’,’g’,’h’,’i’,’j’,’k’,’l’,’m’,’n’,’o’,’p’,’q’,’r’,’s’,’t’,’u’,’v’,’w’,’x’,’y’,’z’)

That might be useful. There is more information here: http://vyaskn.tripod.com/case_sensitive_search_in_sql_server.htm

Leave a Reply

Your email address will not be published. Required fields are marked *