SQL Server LIKE clause issues

Question

This may something really simple I'm overlooking, but it's driving me nuts.

The basic gist is that I have a very simple table in SQL Server 2014, called Products, which contains some ProductNames column (nvarchar(max)). I put in some test strings, such as

Caly
Calyd
Cali
Cal
Taly
Blical
Blacaly

and I'm executing a very simple SELECT on this table:

SELECT *
FROM Products
WHERE ProductName LIKE '%Cal%'

and here's the thing, I'm only getting back these results

Cali
Cal
Blical

Notice the missing Caly, and Blacaly. But if I search for '%Caly%', I get back the expected results:

Caly
Calyd
Blacaly

Or if I search for '%Ca%', again, as expected:

Caly
Calyd
Cali
Cal
Blical
Blacaly

So really, what gives, I'm at a total loss here. Is 'Cal' some kind of weird easter egg word? Are gnomes eating my results, or what?

(Btw,I tried the same thing out in MySQL, just as a test, it works as expected there.)

Answer 1

You are using Hungarian_CI_AS collation. In Hungarian alphabet digraph 'ly' is a separate letter.

So for example "Caly" is a 3-letter word - 'C', 'a', 'ly'.

Answer 2

It's because of your collation, if you look at the Hungarian collation Ly is a character:

Hungarian Collation Chart

And so the like will behave differently, if you really want to treat the data in the Hungarian collated column differently, as in like the Latin1 collation, then you have to tell SQL server this during the select:

SELECT *
FROM Products
WHERE ProductName COLLATE Latin1_General_CI_AS LIKE '%Cal%'