SQL Server LIKE Operator

Like operator is used to search a specific pattern in a column value.

Syntax for like operator: match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ]

match_expression: is a character expression such as a column field

pattern: is a specific string of characters to search for match_expression
It can be maximum of 8000 bytes
Pattern can have these wild card

Wildcard character	Description
% (percentage)	0 or more characters
_ (underscore)	1 character
[] (square bracket)	any single character withing given range [defgh] or [d-h]
[^] (square bracket with negate)	any single character not within the specific range [^d-h] or [^defgh]

escape_character: a character expression that has no default and must evaluate to only one character.
It's optional

LIKE return TRUE if the match_expression matches the specific pattern

POINT TO PONDER:

LIKE operator supports ASCII as well as Unicode pattern matching.

when all the arguments with LIKE operator (match_expression, pattern [escape_character if present]) are ASCII character data types, then ASCII pattern matching is performed.

If any of the arguments of LIKE operator is UNICODE data type, all arguments are converted to UNICODE, and the pattern matching is performed using UNICODE pattern matching.

nchar, nvarchar, ntext are UNICODE data types
Unicode pattern matching with LIKE operator, trailing spaces are significant. Unicode LIKE is compatible with the ISO standard.

ASCII pattern matching with LIKE operator, trailing space is not significant. ASCII LIKE is compatible with earlier versions of SQL Server.

Let's try to understand the UNICODE & ASCII pattern matching

------ ASCII pattern matching with char column (with 15 space)
CREATE TABLE ASCIIPatternTestTable (Name char(15));
INSERT INTO ASCIIPatternTestTable VALUES ('Ram Kumar');

-- data insert will be 'Ram Kumar      ' (means some blank space in right-hand-side)

SELECT * 
FROM ASCIIPatternTestTable 
WHERE Name LIKE '% Kumar';   -- returns 1 row

------ Unicode pattern matching with nchar column
CREATE TABLE UNICODEPatternTestTable (Name nchar(15));
INSERT INTO UNICODEPatternTestTable VALUES ('Ram Kumar');

-- data insert will be 'Ram Kumar      ' (means some blank space in right-hand-side)
SELECT * 
FROM UNICODEPatternTestTable
WHERE Name LIKE '% Kumar';   -- no rows returned

------ Unicode pattern matching with nchar column and RTRIM
CREATE TABLE t (col1 nchar (15));
INSERT INTO t VALUES ('Ram Kumar');

-- data insert will be 'Ram Kumar      ' (means some blank space in right-hand-side)
SELECT * 
FROM t 
WHERE RTRIM(col1) LIKE '% Kumar';   -- returns 1 row

Another point to keep in mind that like operator is also affected by collation.

Let's see some of the examples with the uses of different wildcard of LIKE operators

---

1. using '%' (percentage) wildcard in like operator. % is used to find the 0 or more characters

      -- get all exmployees whose name start with "R"
      SELECT Id, Name 
      FROM Employees
      WHERE Name like 'R%';
      
      
      We can use % multiple times
      -- get all employees whose name is having "m" character in his name
      SELECT Id, Name
      FROM Employees
      WHERE Name like '%m%';
      

2. using _ (underscore) wildcard in like operator. _ is used to find the 1 character

      -- get all employees where name field is having at least one character between 'R' & 'M'
      -- and which starts with R & ends with M
      SELECT Id, Name
      FROM Employees
      WHERE Name like 'R_M';
      
      we can use _ multiple times along with % wildcard as well
      -- get the result-set of all employees whose name field is having at least two charater between 'R' & 'M'
      SELECT Id, Name
      FROM Employees
      WHERE Name like '%R__M%';
      

3. using [] (square bracket) wildcard. [] is used to find any single character within the given range

      -- get all employees whose name field is having one character eiher 'a' or 'i' in-between "R" & "MA"
      SELECT Id, Name
      FROM Employees
      WHERE Name like 'R[ai]MA';  -- RAMA or RIMA
      

4. using [^] (square bracket with NOT) wildcard. [^] to negate the result-set from [] operator

       -- get all employees whose name field is not having one character eiher 'a' or 'i' in-between "R" & "MA"
      SELECT Id, Name
      FROM Employees
      WHERE Name like 'R[ai]MA';  -- except RAMA or RIMA
      

5. using NOT operator with LIKE

      -- get all the employees whose name doesn't starts with "R"
      SELECT Id, Name
      FROM Employees
      WHERE Name NOT LIKE 'R%' ;
      

6. using ESCAPE character Escape charater is being used to search for special character

      -- get all the employees where employee-code field is having [rm-%]
      
      SELECT Id, Name 
      FROM Employees
      WHERE EmployeeCode LIKE '\[rm-%\]' ;