SQL LIKE operator with aaa% having strange behavior

In my case I have patient table with few columns including patient’s firstname and lastname. Application using this table having patient search functionality with firstname and lastname.

Having some test data in table with lastname ended with few letters of consecutive ‘a’ like in below image

So when I run below simple query it having some strange result

select * from A_Patient where Firstname like 'aaa%'- **0 Row**
select * from A_Patient where Firstname like 'aaaa%'- **7 Row**
select * from A_Patient where Firstname like 'aaaaa%'- **1 Row**
select * from A_Patient where Firstname like 'aaaaaa%' - **3 Row**
select * from A_Patient where Firstname like 'aaaaaaa%' - **0 Row**
select * from A_Patient where Firstname like 'aaaaaaaa%' - **3 Row**

So when having ‘a’ is even number it result all result. but when it odd number of characters it returns only if having extract number of character matching. otherwise it return none. I changed letter ‘a’ to ‘b’ and ‘c’ tested but this behaviors was not found.

DataType of columns are nvarchar. Any idea why this strange behaviors happen?

Answer

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.

Advertisement

Answer