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!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 29, 2017 at 6:50 am
This was removed by the editor as SPAM
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 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply