Search code examples
sqlsql-servert-sqlwildcardsql-like

SQL Like with wildcard returns wrong result on certain letters

I can't figure out why am i getting wrong result when using like keyword with wildcard. For the sake of discussion I've made small example to illustrate my problem. Let's say we have some table

CREATE TABLE TestingLike(
[Name] VARCHAR(50)  
)

INSERT INTO TestingLike VALUES ('A'), ('AB'), ('L') ,('LJ')

Now when I use this search criteria 

SELECT [Name] FROM TestingLike WHERE [Name] LIKE 'A%'

SELECT [Name] FROM TestingLike WHERE [Name] LIKE 'L%'

First query returns (A, AB) while second returns just L. Strangely this is not happening when using temporary tables

For the love of god can someone please explain why is this happening, I've done some research, but could not found the answer, I am using SQL SERVER RC1 2017 as well as SQL SMS.

Thank you for your time.

Below is the picture of execution plan

enter image description here

Solution

  • I cannot reproduce with a Latin collation but can if I'm using a Croatian collation, because in Croatian (and some other Slavic languages1), Lj is a single distinct letter formed from two characters, and is not L followed by j. Lj (digraph) on wikipedia discusses this

    You would not experience the same issue with temp tables/table variables if the collation for tempdb is different to the default collation for this specific database exhibiting the problem.

    Complete/self contained repro script for those playing at home:

    declare @TestingLike table(
[Name] VARCHAR(50) collate Croatian_CI_AI
)

INSERT INTO @TestingLike VALUES ('A'), ('AB'), ('L') ,('LJ')

SELECT [Name] FROM @TestingLike WHERE [Name] LIKE 'A%'

SELECT [Name] FROM @TestingLike WHERE [Name] LIKE 'L%'

    How you go about "fixing" this depends on your overall goal. Your code is working correctly if it's playing by Croatian rules. Otherwise, you may need to change the default collation of the database. But, the default collation only applies when a column is created. So you'll also have to alter every existing column to change the collation to what you need.

    1I use Croatian throughout my answer but I do, of course, mean "any collation where Lj is a digraph".