SQL Like a single letter with a pattern

Question

I have a problem with SQL query in SQL Database. I can’t just understand why.

I would like to get all the lines there. ShortName contains 'A'.

The ShortName column has a data type nvarchar(max).

My table looks like this:

If I make this query in the database: SELECT ID, ShortName FROM Airports where ShortName LIKE '%A%';

Results:

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

Results:

How can you do this, not all lines there contain "A". But why? I am looking for an answer, but I can not find it.

Answer 1

Since you are in Scandinavia, your default sorting for the database is most likely Danish_Norwegian_CI_AS. This will cause AA to be interpreted as Å

So for your request to work, use:

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

Answer 2

, , , , AA - (, )
, .

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

AAR .

Answer 3

script ?

:

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%';

:

         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)

Answer 4

, :

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

, ..