umlauts, accents etc 2

[arst06d]

How can I get sql server to assume that e is the same as e with an acute or grave accent, or that o is the same as o with umlaut when querying?

Something to do with collation?

Thanks

 

[vongrunt]

Yup, accent-insensitive collation (collation name ends with 'AI'). You can set it at DB level, column level or in query like this one:

select case when 'Ö' collate SQL_Latin1_General_CP1_CI_AS = 'O' then 'yep' else 'nope' end
select case when 'Ö' collate SQL_Latin1_General_CP1_CI_AI = 'O' then 'yep' else 'nope' end

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[arst06d]

Hi & thanks for the reply. Still not quite clear on how this would work.

If I wanted to do

Select * from tblPeople
Where lastname='Gross'

how would I formulate that query to return recors where lastname is 'Gross' as well as 'GrÖss'?

Thanks

 

[SQLDenis]

here take a look

create table #tblPeople (lastname varchar(50))
insert into #tblPeople
select 'Gross' union all
select 'GrÖss'

Select * from #tblPeople
Where lastname collate SQL_Latin1_General_CP1_CI_AI  like 'Gross'

Select * from #tblPeople
Where lastname collate SQL_Latin1_General_CP1_CI_AI  like 'GrÖss'

Select * from #tblPeople
Where lastname like 'Gr[oÖ]ss'

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

btw you can use = instead of like for the first 2 queries

Select * from #tblPeople
Where lastname collate SQL_Latin1_General_CP1_CI_AI  = 'Gross'

Select * from #tblPeople
Where lastname collate SQL_Latin1_General_CP1_CI_AI  = 'GrÖss'

Select * from #tblPeople
Where lastname like 'Gr[oÖ]ss'

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

That's one possibility... another one is to define column collation (manually or with EM) and then COLLATE in queries would not be necessary... of course this is best done when DB is created, later patches can get ugly.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[mrdenny]

I would recommend changing the column colation to the updated collation level. It may be a bit harder in the short term, however it will allow SQL Server to continue to use any indexes which are on that column (after you recreate them).

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[arst06d]

Fantastic - thanks very much to all.

 

[arst06d]

works fine on sql 2000 but doesn't seem to work on sql 7
get incorrect syntax error

any way of doing this on sql7?

 

[vongrunt]

Ack...

sql7 supports only collation at server level

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[arst06d]

oh well, it will have to wait
we will be converting in a month or so anyway.

Thanks

 

[mrdenny]

You can change the collation on SQL 7, however it's not for the feint of heart as you have to rebuilt the master database, then recreate all the logins, and attach the databases.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com