Today, I got a question from customer:

I have one column (Type: nVarchar) in a table which has data something like this:

a1

aa

aa2

AB

I want to show all the rows which have all the lower 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 uppercase 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.