My database COLLATE is SQL_Latin1_General_CP1_ CS _AS, then create a table. insert records into table. The Identifier are
aaaa
AAAA
bbbb
BBBB
cccc
CCCCwhen execute "select * from TABLE1 where Identifier between 'A' and 'a';" there are no recordd fetched. My expected result is: AAAA,BBBB,CCCC
where execute "select * from ASDB.dbo.ASDB where Identifier between 'A' and 'B';", record aaaa can be fetched. My expected result is: AAAA, BBBBWHY?
I think because my COLLATE is case sentive, why this happens?
aaaa
AAAA
bbbb
BBBB
cccc
CCCCwhen execute "select * from TABLE1 where Identifier between 'A' and 'a';" there are no recordd fetched. My expected result is: AAAA,BBBB,CCCC
where execute "select * from ASDB.dbo.ASDB where Identifier between 'A' and 'B';", record aaaa can be fetched. My expected result is: AAAA, BBBBWHY?
I think because my COLLATE is case sentive, why this happens?
(
Identifier varchar(10) COLLATE SQL_Latin1_General_CP1_CS_AS
)
insert k select
'aaaa' union all select
'AAAA' union all select
'bbbb' union all select
'BBBB' union all select
'cccc' union all select
'CCCC'
go
select * from k where ascii(left(Identifier,1)) between ascii('A') and ascii('B')
/*
Identifier
----------
AAAA
BBBB
*/
select * from k where ascii(left(Identifier,1)) between ascii('A') and ascii('a')
/*
Identifier
----------
aaaa
AAAA
BBBB
CCCC*/
unicode编码
yes this works, but this have limitations.
it can not handle where clause: where Identifier between 'AAA' and 'BB',
it does not work.