SQL 'Like' operator and 'aa'

Question

We've stumbled upon a very odd problem in one of our applications. The seach engine uses a stored procedure to compare a bunch of filters. However when a specific type of string is inserted, the sql server (2005) behaves very odd. I've isolated the problem to the following:

select 'match!' where  'teliaa' like '%telia%'

The collation is Danish Norwegian CI AS and we have characters that mean the same thing. This includes 'aa' which also means 'å'.

Can anyone explain why the above statement does not yeild 'match!'

Answer 1

The collation won't automatically match "aa" to "å".

It will make sure that "å" is sorted correctly and some other stuff but it won't substitute. The same applies "ss" vs "ß" in German, for example

You'd have to clean the data one way or the other.

SELECT REPLACE ('teliå', 'å', 'aa'), /* ...or  */REPLACE ('teliaa', 'aa', 'å')