SQL statement "Like" and "aa"

Question

We encountered a very strange problem in one of our applications. The seach engine uses a stored procedure to compare multiple filters. However, when a particular row type is inserted, the SQL server (2005) behaves very strangely. I highlighted the problem as follows:

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

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

Can someone explain why the above statement does not give "match!"

Answer 1

Matching will not automatically match “aa” with “å”.

He will make sure that å is sorted correctly and some other things, but he will not replace. The same applies to "ss" vs "ß" in German, for example

You will have to clear the data anyway.

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

Edit May 2013

I assume that å does not match aa in this sort. However, it sorts correctly

 DECLARE @foo TABLE (bar varchar(2)) INSERT @foo VALUES ('Ab'),('Aa'),('aa'), ('å'), ('Za'); SELECT * FROM @foo ORDER BY bar COLLATE Danish_Norwegian_CI_AS; SELECT * FROM @foo WHERE bar COLLATE Danish_Norwegian_CI_AS = 'Aa'; SELECT * FROM @foo WHERE bar COLLATE Danish_Norwegian_CI_AS = 'a'; SELECT * FROM @foo WHERE bar COLLATE Danish_Norwegian_CI_AS = 'å'; 

Answer 2

Collation is a set of rules for comparing characters that are most useful for sorting. Some say that this only affects sorting; this is not entirely correct. From https://technet.microsoft.com/en-us/library/aa174903%28v=sql.80%29.aspx

Comparison SQL Server defines how the database engine stores and works with character and Unicode data.

As an example, in Danish_Norwegian accent insensitive comparisons correspond to 'aa' with 'å'. Names starting with "aa" are sorted along with names starting with "å". However, this also affects the comparison and the LIKE operator. Here are a few query lines illustrating this.

 select 'match!' where 'teliaa' collate Latin1_General_100_CI_AI like '%telia%' --yields "match!" select 'match!' where 'teliaa' collate Latin1_General_100_CI_AS like '%telia%' --yields "match!" select 'match!' where 'teliaa' collate Latin1_General_100_CS_AI like '%telia%' --yields "match!" select 'match!' where 'teliaa' collate Latin1_General_100_CS_AS like '%telia%' --yields "match!" select 'match!' where 'teliaa' collate Danish_Norwegian_CI_AI like '%telia%' --no rows select 'match!' where 'teliaa' collate Danish_Norwegian_CI_AS like '%telia%' --no rows select 'match!' where 'teliaa' collate Danish_Norwegian_CS_AI like '%telia%' --no rows select 'match!' where 'teliaa' collate Danish_Norwegian_CS_AS like '%telia%' --no rows select 'match!' where 'teliaa' collate Danish_Norwegian_CI_AI like '%å%' --yields "match!" select 'match!' where 'teliaa' collate Danish_Norwegian_CI_AS like '%å%' --no rows select 'match!' where 'teliaa' collate Danish_Norwegian_CS_AI like '%å%' --yields "match!" select 'match!' where 'teliaa' collate Danish_Norwegian_CS_AS like '%å%' --no rows 

Depending on your needs, if you need to match "aa" with "å", treating them the same way, then select the mapping that supports this in your query or data warehouse / columns. If not, select a sort, for example Latin1_General_100_CI_AI.

Answer 3

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

gives a "match!" as output on my sql-2008 (SP1) 10.0.2531.0 (x64)

I can not answer your question ...