Today, I got a question from customer:
I have one column (Type: nVarchar) in a table which has data something like this:
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.