INFO: Use COLLATE for case-sensitive (CS) comparisons in a CI database
From: Aaron W. West (tallpeak_at_hotmail.NO.SPAM)
Date: 06/24/04
- Next message: Tom Moreau: "Re: using OpenXML in T-sql?"
- Previous message: Joe Celko: "Re: Help with 2 queries / Join problem"
- Next in thread: Aaron [SQL Server MVP]: "Re: Use COLLATE for case-sensitive (CS) comparisons in a CI database"
- Reply: Aaron [SQL Server MVP]: "Re: Use COLLATE for case-sensitive (CS) comparisons in a CI database"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 24 Jun 2004 16:21:17 -0700
You can use COLLATE to force case-sensitive comparisons in WHERE clauses,
GROUP BY, etc.
create table t(x varchar(20))
insert into t select 'John' union all select 'JOHN' union all select 'john'
select distinct x from t
x
--------------------
John
(1 row(s) affected)
select distinct x COLLATE SQL_Latin1_General_Cp437_BIN from t
--------------------
JOHN
John
john
(3 row(s) affected)
select distinct x COLLATE Latin1_General_BIN from t
--------------------
JOHN
John
john
(3 row(s) affected)
For a list of collation names, see BOL topic SQL collation names, as well as
Windows collation names.
This article mentioned the technique of using CONVERT(binary(5),x), which is
apparently necessary for older versions of SQL (pre-2000):
http://support.microsoft.com/default.aspx?scid=kb;en-us;171299
Also see:
SELECT SERVERPROPERTY('collation')
exec sp_helpsort
------------------
Try this to see all collations:
SELECT *
FROM ::fn_helpcollations()
------------------------
To see the ordering of characters in all collations, you could run this.
I see that even accent-sensitive collations put most accented characters
near the corresponding un-accented ones.
I think the the collation COLLATE Latin1_General_BIN is the only truly
binary collation.
--alter proc show_all_collations as
set ansi_nulls on
select top 224 identity(int, 32, 1) as anint
into #nums
from pubs..authors a, pubs..authors b
declare collcurs CURSOR FOR
SELECT name
FROM ::fn_helpcollations()
set nocount on
open collcurs
declare @collname varchar(35)
declare @a nvarchar(256)
FETCH collcurs into @collname
while @@fetch_status = 0
begin
set @a=''
drop table tempdb.dbo.chars
exec('select achar = char(anint) COLLATE '+@collname+'
into tempdb.dbo.chars from #nums
order by char(anint) COLLATE '+@collname )
if exists(select * from tempdb.dbo.sysobjects where name='chars')
select @a=@a+achar from tempdb.dbo.chars
else
select @a='ERROR BUILDING COLLATION'
print left(@collname + ' ',35) + @a
--'SQL_Latin1_General_Cp437_BIN'
FETCH collcurs into @collname
end
set nocount off
close collcurs
deallocate collcurs
drop table tempdb.dbo.chars
GO
--- Hmm, did you know there are 240 ways to spell John? Excuse me.... select top 224 identity(int, 32, 1) as anint into #nums from pubs..authors a, pubs..authors b select j.l+o.l+h.l+n.l FROM (select l=char(anint) from #nums where char(anint) COLLATE Latin1_General_CI_AI like 'j') j, (select l=char(anint) from #nums where char(anint) COLLATE Latin1_General_CI_AI like 'o') o, (select l=char(anint) from #nums where char(anint) COLLATE Latin1_General_CI_AI like 'h') h, (select l=char(anint) from #nums where char(anint) COLLATE Latin1_General_CI_AI like 'n') n
- Next message: Tom Moreau: "Re: using OpenXML in T-sql?"
- Previous message: Joe Celko: "Re: Help with 2 queries / Join problem"
- Next in thread: Aaron [SQL Server MVP]: "Re: Use COLLATE for case-sensitive (CS) comparisons in a CI database"
- Reply: Aaron [SQL Server MVP]: "Re: Use COLLATE for case-sensitive (CS) comparisons in a CI database"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
