Why SQL Server has error when i use LIKE operator?-CodePudding

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

CodePudding user response：

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

CodePudding user response：

your data

declare @employee TABLE (
   fname VARCHAR(20) NOT NULL 
  ,lname VARCHAR(20) NOT NULL
);
INSERT INTO @employee(fname,lname) VALUES ('Helen','Bennett');
INSERT INTO @employee(fname,lname) VALUES ('Helvetius','Nagy');
INSERT INTO @employee(fname,lname) VALUES ('Palle','Ibsen');
INSERT INTO @employee(fname,lname) VALUES ('Philip','Cramer');
INSERT INTO @employee(fname,lname) VALUES ('Roland','Mendel');

for searching in column for specific charachter using CHARINDEX and SUBSTRING would be much better. SUBSTRING

select concat(fname,' ',lname) as fullname
FROM @employee  e  
where SUBSTRING(fname, 3, 1)='l'
ORDER BY e.fname

CHARINDEX

select concat(fname,' ',lname) as fullname
FROM @employee  e  
where CHARINDEX('l', fname) = 3
ORDER BY e.fname