Possible SQL Server 2005 bug.....
-
.... has to do with collations. I just found this behavior while looking for a bug in my code.
Basically, LIKE operator returns TRUE when 'aaa' compared to '[A-Z]%' in case-sensitive fashion. It should be returning FALSE.
(collation on the server is default. SQL Server 2005 Developer, SP2)
Am I insane????
Take a look:
declare
@s-2 varchar(50)
set
@s-2 = 'aaa'
select
@s-2 where @s-2 collate sql_latin1_general_cp1_cs_as LIKE '[A-Z]%' collate sql_latin1_general_cp1_cs_as
declare
@t table (s varchar (50) collate sql_latin1_general_cp1_ci_as)
insert
@t select 'aaa'
insert
@t select 'AAA'
select
* from @t
where
s collate sql_latin1_general_cp1_cs_as LIKE '[A-Z]%' collate sql_latin1_general_cp1_cs_as
select
* from @t
where
s collate sql_latin1_general_cp1_cs_as LIKE '[a-z]%' collate sql_latin1_general_cp1_cs_as
declare
@t2 table (s varchar (50) collate sql_latin1_general_cp1_cs_as)
insert
@t2 select 'aaa'
insert
@t2 select 'AAA'
select
* from @t2
where
s collate sql_latin1_general_cp1_cs_as LIKE '[A-Z]%' collate sql_latin1_general_cp1_cs_as
select
* from @t2
where
s collate sql_latin1_general_cp1_cs_as LIKE '[a-z]%' collate sql_latin1_general_cp1_cs_as
--------------------------------------------------
aaa
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
s
--------------------------------------------------
aaa
AAA
(2 row(s) affected)
s
--------------------------------------------------
aaa
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
s
--------------------------------------------------
aaa
AAA
(2 row(s) affected)
s
--------------------------------------------------
aaa
(1 row(s) affected)
-
The Problem is that [A-Z] != [ABCDEFGHIJKLMNOPQRSTUVWXYZ] When COLLATE is used
[A-Z] apparently means: AaÁáÀàÄäBbCcÇç ... wWxX... When COLLATE is used.
NOTE: I can't back this up with any documentation but this has been *my* experience.
You could try [ABCDEFGHIJKLMNOPQRSTUVWXYZ] to get the results you want.
Hope this helps
* Noel -
Thanks. I get it now.
It is just not obvious and not documented up front (as far as I can see).
-
totally agree.
* Noel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply