german umlauts and collate - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

german umlauts and collate

Author

Topic

gonk
Starting Member

8 Posts

Posted - 2005-02-24 : 13:09:47

hi there,

the task is easy. in germany we have "umlauts". not only 26 characters but some more. for every of this special characters there is a "long version". you can write "müller" and its the same as "mueller". (probably you won't be able to read the umlaut in the first mueller, because of you charset). its the same with accents in other languages.

i want to write a query that finds both mueller and müller. i solved the problem without sql by using ors:

where name like 'müller' or name like 'mueller'

but thats a bit slow. i thought about using the collate statement. will this work ?

thanks chris

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-24 : 22:01:28

What do you mean by "using the collate statment"?

Depending on how many possible alternalte spellings you have, one possiblility is to create a seperate table that you could add to your join which contains your alternatives. Without the ORs and LIKEs and properly indexed it could be quite fast. Here is what I mean using names and "nicknames". If you like the idea, you might be able to find a database of alternate spellings to download from somewhere.


set nocount on
declare @People Table
	(FirstName varchar(30))

declare @AlternateSpelling Table
	(FirstName varchar(30)
	,NickName varchar(30))

insert @People 
Select 'Theodore' union
Select 'Jonathon' union
Select 'William' union
Select 'Susan' union
Select 'Elizabeth'

insert @AlternateSpelling
Select 'Theodore', 'Ted' UNION
Select 'Theodore', 'Teddy' union
Select 'Theodore', 'Theo' union
Select 'Theodore', 'Theodore' UNION
Select 'Jonathon', 'Jon' union
Select 'Jonathon', 'Jonny' union
Select 'Jonathon', 'John' union
Select 'Jonathon', 'Jonathon' union
Select 'William', 'Bill' union
Select 'William', 'Willy' union
Select 'William', 'Will' union
Select 'William', 'William' 

--you'd have to experiment with the best query structure given the size of you tables, indexes etc...
Select	p.FirstName
From	@People p
Left JOIN @AlternateSpelling a
		ON p.FirstName = a.FirstName
Where	'Elizabeth' = isnull(a.Nickname,p.FirstName)

Be One with the Optimizer
TG

gonk
Starting Member

8 Posts

Posted - 2005-02-28 : 04:11:25

hi,

sometimes its funny: on a friday you spend hours on a problem and on monday it takes you one minute to solve it:

using a query like

select * from member where name like 'müller' COLLATE German_PhoneBook_CI_AI

it will find "müller", "mueller", "Müller" etc. exactly what i was searching for !

chris

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources