SQL Server Case Sensitive Query

Someone at work sent this out to the team. It might be useful some time:

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’)

Thanks. That might be useful. There is more infomation here:

http://vyaskn.tripod.com/case_sensitive_search_in_sql_server.htm

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: