CODEWITHSUNDEEP
sql-server
CodingDK
CodingDK

Reputation: 163

SQL Like single letter with wildcard

I have a problem with a SQL query to SQL Database. I can't simple find out why.

I will like to get all the rows there ShortName contains 'A'.

The ShortName column has datatype nvarchar(max).

My table look like this:

If i do this query on the database: SELECT ID, ShortName FROM Airports where ShortName LIKE '%A%';

Results:

If I do this query: SELECT ID, ShortName FROM Airports where ShortName LIKE '%AA%';

Results:

As you can se it not takes all the rows there shortname contains 'A'. But why? I have searching for the answer, but I can't find it.

Upvotes: 4

Views: 307

Answers (4)

ErikEJ
ErikEJ

Reputation: 41819

Since your are in Scandinavia, your database default collation is most likely Danish_Norwegian_CI_AS. This will cause AA to be interpeted as Å

So for your query to work, use:

SELECT ID, ShortName FROM Airports 
where ShortName LIKE '%A%' 
COLLATE Latin1_General_CI_AS;

Upvotes: 4

Allan S. Hansen
Allan S. Hansen

Reputation: 4091

It comes down to which collation you use in the database, I would guess because AA can be interepetated differently in some language (Danish for example)
If you do the following you'll get different result for each of the selects.

DECLARE @t TABLE (v VARCHAR(max))
INSERT INTO @t VALUES ('AAR')
SELECT * FROM @t WHERE v COLLATE SQL_Latin1_General_CP1255_CS_AS LIKE '%A%' COLLATE SQL_Latin1_General_CP1255_CS_AS

SELECT * FROM @t WHERE v COLLATE Danish_Norwegian_CI_AI LIKE '%A%' COLLATE Danish_Norwegian_CI_AI

This gives me result AAR from the first and nothing in this second.

Upvotes: 1

David Defossez
David Defossez

Reputation: 11

strange , when I execute your first query:

CREATE TABLE  testtt
(
ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
ShortName nvarchar(max),
);

insert into testtt (ShortName) values ('AAR');
insert into testtt (ShortName) values ('BLL');
insert into testtt (ShortName) values ('CPH');
insert into testtt (ShortName) values ('EBJ');
insert into testtt (ShortName) values ('KRP');
insert into testtt (ShortName) values ('ODE');
insert into testtt (ShortName) values ('RKE');
insert into testtt (ShortName) values ('RNN');
insert into testtt (ShortName) values ('SGD');
insert into testtt (ShortName) values ('SKS');
insert into testtt (ShortName) values ('TED');
insert into testtt (ShortName) values ('STA');
insert into testtt (ShortName) values ('AAL');
insert into testtt (ShortName) values ('CNL');
insert into testtt (ShortName) values ('WGH');


SELECT ID, ShortName
FROM testtt
WHERE ShortName LIKE '%A%'

SELECT * FROM Testtt

I get the desired results though..

Upvotes: -1

milivojeviCH
milivojeviCH

Reputation: 1608

Could you script the table and the data?

If I do this:

SELECT CAST(1 as int) AS ID, CAST('AAR' AS nvarchar(MAX)) AS ShortName INTO #Airports

INSERT INTO #Airports
SELECT 2, 'CPH' UNION ALL
SELECT 3, 'STA' UNION ALL
SELECT 4, 'AAL' UNION ALL
SELECT 5, 'CNL' UNION ALL
SELECT 6, 'ZGH' UNION ALL
SELECT 7, 'ROD' UNION ALL
SELECT 8, 'KRP' 

SELECT * FROM #Airports a
SELECT ID, ShortName FROM #Airports where ShortName LIKE '%A%';
SELECT ID, ShortName FROM #Airports where ShortName LIKE '%AA%';

I do get expected results:

         ID ShortName
----------- ----------------------------------------------------------------
          2 CPH
          3 STA
          4 AAL
          5 CNL
          6 ZGH
          7 ROD
          8 KRP
          1 AAR

(8 row(s) affected)

         ID ShortName
----------- ----------------------------------------------------------------
          3 STA
          4 AAL
          1 AAR

(3 row(s) affected)

         ID ShortName
----------- ----------------------------------------------------------------
          4 AAL
          1 AAR

(2 row(s) affected)

Upvotes: 0

Related Questions