Search

Friday, May 11, 2012

How to make Like case sensitive

I have one column (Type: nVarChar) in a table which has data something like this:
A1
A2
A3
a4
a5
A6
B1
b2
CA
CB
c9


I want to show all the rows which have all the upper case letters in the above mentioned column.


The following query works:


select * from test WHERE LTRIM(RTRIM(A)) LIKE '%[ABC]%' Collate Latin1_General_CS_AI  


And Surprisingly this one does not:


select * from test WHERE LTRIM(RTRIM(A)) LIKE '%[A-C]%' Collate Latin1_General_CS_AI  --Would actually be [A-Z]


Is it like the case sensitivity does not work with ranges i.e. [A-C] or I am missing something here?


Here is my answer:


Can you try


select * from test WHERE LTRIM(RTRIM(A)) LIKE '%[A-C]%' Collate Latin1_General_BIN


  this should give you case sensitive result.


The reason is that  [A-C] means include all characters which is bigger or equal than A, and smaller than C,  so lowercase a is also in the range even according to linguistic order for Latin1_General_CA_A. In other case, for case sensitive collation,  A is not equal to a, not  the order of the characters might still be  a <A <b < B <c <C, etc.

No comments:

Post a Comment