Problems with the SQL Server LIKE clause

Question

It may be something really simple that I am missing, but it makes me go blank.

The main point is that I have a very simple table in SQL Server 2014 called Products, which contains the column ProductNames (nvarchar (max)). I put some test lines like

Caly
Calyd
Cali
Cal
Taly
Blical
Blacaly

and I perform a very simple SELECT on this table:

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

and here’s what, I’m only returning these results

Cali
Cal
Blical

Check out the missing Caly and Blacaly . But if I search for "% Caly%", I will return the expected results:

Caly
Calyd
Blacaly

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

Caly
Calyd
Cali
Cal
Blical
Blacaly

So what gives, I'm here with complete loss. Is the feces some kind of weird easter egg? Dwarves eat my results, or what?

(Btw, MySQL, , , .)

Answer 1

Hungarian_CI_AS. digraph 'ly' - .

, , "Caly" - 3- - "C", "a", "ly".

Answer 2

- , Ly :

, , -, -, , SQL- :

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