March 29, 2017 at 5:08 am
Our DBs are universally set as having a case insensitive collation, but for one query I needed it to be case sensitive, so I added the latin1_general_CS_AS collation sequence to the table variable column and got matches when I don't think I should. Here's an example of the kind of thing..
DECLARE @T table (data char(1) collate Latin1_General_CS_AS)
Insert @T (data) values ('A')
Insert @T (data) values ('a')
Insert @T (data) values ('B')
Insert @T (data) values ('b')
select * from @T where data like '[ABCDEFG]'
select * from @T where data like '[A-G]'
The first select returns what I'd expect, just rows 'A' and 'B' but the 2nd one returns 'A', 'B' and 'b'. I thought that the A-B in the like meant range ?
March 29, 2017 at 5:37 am
Is it because a comes before A and therefore [A-G] doesn't include a?
John
March 29, 2017 at 5:56 am
This might help..
http://stackoverflow.com/questions/24209826/why-is-latin1-general-cs-as-not-case-sensitive
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 29, 2017 at 6:08 am
Kingston Dhasian - Wednesday, March 29, 2017 5:56 AM
Thanks for the link, that does appear to explain it, though it's a bit strange!
March 29, 2017 at 4:08 pm
but ...
DECLARE @T table (data char(1))
Insert @T (data) values ('A')
Insert @T (data) values ('a')
Insert @T (data) values ('B')
Insert @T (data) values ('b')
select * from @T where data like '[ABCDEFG]'
select * from @T where data like '[A-G]' collate Latin1_General_BIN
does give the desired result. And no need to change the table definition
March 30, 2017 at 10:05 pm
frederico_fonseca - Wednesday, March 29, 2017 4:08 PMbut ...
DECLARE @T table (data char(1))Insert @T (data) values ('A')
Insert @T (data) values ('a')
Insert @T (data) values ('B')
Insert @T (data) values ('b')
select * from @T where data like '[ABCDEFG]'
select * from @T where data like '[A-G]' collate Latin1_General_BINdoes give the desired result. And no need to change the table definition
Yes. The results depend upon the sorting rules of the collation. The sorting rules for collation "Latin1_General_CS_AS
" and "Latin1_General_BIN"
are different and hence, the difference in results as per Microsoft. The Connect Item below will give a bit more details.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy