A simple query in sql server does not return a result

Question

I have a simple LIKE query on sql server that does not return the result when the main character matches the first ...

The database contains a row with the "Last Name" field and the value "Aalesund"

select * from table where LastName like 'A%' -- returns no results 

if I change the "Lastname" field to contain "Alesund", the same query returns the correct string.

Any ideas?

Stig

Answer 1

In the Danish / Norwegian Sql server comparison, “aa” is considered a “synonym” for å, since this letter was written in Denmark before the 1940s. It also means that "aa" is considered the last letter of the alphabet when sorting.

I don’t know if this value makes sense in the Norwegian context, but I think the sorting is split into two in SQL Server 2012, maybe this will fix it.

UPDATE: According to Wikipedia, the use of “Aa” as “Å” in Norway is similar to that in Denmark: “Aa” was canceled by official organizations standardizing languages ​​for several decades, but some cities refuse to change their names for historical reasons, and “Aa” also still common in family names.

Based on this, I would say that the inability to find "Aalesund" when performing a LIKE search of "A%" is the most correct behavior in a Norwegian-localized application. If you have refused to use Danish / Norwegian collation to change this, you should know that it will change other properties, such as sorting special letters as described above.

Many different combinations called Danish_Norwegian _... will not help you AFAIK. They define behavior with respect to case sensitivity, accents, character width and channel type (the latter, in my opinion, applies only to the Japanese). “Å” is not considered accented by “A”, but as a separate letter. For a description of naming conventions for mappings, see this MSDN article .

UPDATE 2: Surprisingly, the Norwegian_100_CI_AI collation does what you want. Maybe the use in Denmark and Norway is still different ...

Answer 2

This name is sometimes spelled "Ålesund" The small circle above A is very small in some fonts. You may inadvertently copy / paste "Ålesund" when you try the query with the full name. Try

 select * from table where LastName like 'Å%'