SQL 'Like' Operator and 'Aa'

SQL LIKE operator with aaa% having strange behavior

Please see this test case:

DECLARE @TestTable TABLE
(
    id INT IDENTITY(1, 1)
,   LastName NVARCHAR(255) COLLATE Danish_Norwegian_CI_AS
)

INSERT @TestTable (LastName)
VALUES  (N'aa')
,       (N'aaa')
,       (N'aaaa')

SELECT  *
FROM    @TestTable AS TT
WHERE   TT.LastName LIKE N'aaa%' COLLATE Latin1_General_CI_AS

Without the COLLATE Latin1_General_CI_AS you get the behaviour you are seeing.

What does like'%aa%' do in sql?

It search for a record in which the field in the condition contains the literal, in this case 'aa'.

For instance:

Select * from Employee
where Name like '%aa%'

It retrieves all employees where the names contains the literal 'aa'..

Regards!!!

Simple like query in sql server return no result

In the Danish/Norwegian collation of Sql server, "aa" is considered a "synonym" for å, Since that was how the letter was written in Denmark before the 1940es. This also means that "aa" is considered the last letter of the alphabet when sorting.

I don't know whether this behavior makes sense in a Norwegian context, but I think the collation is being split in two in Sql server 2012, maybe that will fix it.

UPDATE: According to Wikipedia, the usage of "Aa" as "Å" in Norway is similar to the Danish usage: "Aa" has been abolished by the official organizations standardizing the languages for several decades, but some towns refuse to change their names for historical reasons, and "Aa" is also still common in family names.

Based on this, I would say that failing to find "Aalesund" when doing a search for LIKE "A%" is the most correct behavior in a Norwegian-localized application. If you refrain from using the Danish/Norwegian collation in order to change this, you should be aware that it will change other properties such as sorting of special letters, as mentioned above.

The many different collations named Danish_Norwegian_... will not help you AFAIK. They specify behaviour regarding sensitivity to case, accents, character width, and kana type (the latter is only relevant in Japanese, I believe). "Å" is not considered an accented "A", but a distinct letter. A description of the naming conventions for collations is in this MSDN article.

UPDATE 2: Surprisingly, it seems the collation Norwegian_100_CI_AI does what you want. Maybe the usage in Denmark and Norway is different after all...

SQL LIKE with special characters

Most likely some collation or datatype issue

Example, gives 97 and 230

SELECT ASCII('æ' COLLATE Albanian_CI_AI), ASCII('æ' COLLATE Latin1_General_CI_AS) 

We'll need more info basically.

Edit: Question about Danish/Norwegian å (although unresolved)

Edit 2: change the code to this if name is nvarchar so the literal becomes unicode too.

Select * from users where name like N'%æ%'

SQL query to check if a name begins and ends with a vowel

You could use a regular expression:

SELECT DISTINCT city
FROM   station
WHERE  city RLIKE '^[aeiouAEIOU].*[aeiouAEIOU]$'

How can I introduce multiple conditions in LIKE operator?

Here is an alternative way:

select * from tbl where col like 'ABC%'
union
select * from tbl where col like 'XYZ%'
union
select * from tbl where col like 'PQR%';

Here is the test code to verify:

create table tbl (col varchar(255));
insert into tbl (col) values ('ABCDEFG'), ('HIJKLMNO'), ('PQRSTUVW'), ('XYZ');
select * from tbl where col like 'ABC%'
union
select * from tbl where col like 'XYZ%'
union
select * from tbl where col like 'PQR%';
+----------+
| col      |
+----------+
| ABCDEFG  |
| XYZ      |
| PQRSTUVW |
+----------+
3 rows in set (0.00 sec)

---

Related Topics