Why SQL Server has error when i use LIKE operator?

Question

I want to select employees that have the third character of the first name is ‘l’ (as image below) After executing, there are 4 correct record and 1 incorrect. I dont't understand why that record having first name is 'Philip' with l is fourth character is selected?

My SQL Statements

Answer 1

Yes this is a COLLATION specific probleme and those who gave a negative rating indiscriminately should think a little more than instinctively vote!

With the Vietnamese_100_... collation (which I think that is the case for our user HaNgocHieu) or for some others collations like Welsh_100... the Ph two letters are considered as only one and the result is that the query returns also Philip.

As a test :

CREATE TABLE #employee  
( fname VARCHAR(20) NOT NULL,
  lname VARCHAR(20) NOT NULL);

INSERT INTO #employee(fname,lname) 
VALUES ('Philip','Cramer');

SELECT *
FROM  #employee  e  
where fname + lname COLLATE Vietnamese_CI_AI LIKE '__l%';

fname                lname
-------------------- --------------------
Philip               Cramer

SELECT *
FROM  #employee  e  
where fname + lname COLLATE French_CI_AI LIKE '__l%';

fname                lname
-------------------- --------------------

SELECT *
FROM  #employee  e  
where fname + lname COLLATE Welsh_100_CI_AI LIKE '__l%';


fname                lname
-------------------- --------------------
Philip               Cramer

So SQL Server has no error, nor HaNgocHieu does not make any mistake, but using a specific collations with non specific data can cause some trouble that can be solved in using an international COLLATION like those in latin