SQL Server LIKE Operator

This SQL tutorial focuses on the SQL Server LIKE logical operator, and provides explanations, examples and exercises. For this lesson’s exercises, use this link.

This tutorial is a part of several posts explaining how to use the WHERE clause in SQL Server. To read additional posts regarding this subject, please use the following links:

• Introduction – SQL Server WHERE clause
• Comparison operators
• IN operator
• BETWEEN operator
• LIKE operator
• IS NULL operator
• AND & OR operators

SQL Server LIKE Operator

In SQL Server, You may not always know the exact value to search for, sometimes you may want to select rows that match a certain character pattern, for example:

• All employees whose name starts with ‘M’ (First Name column).
• All customers whose mobile phone starts with 054 (Cell Phone column).
• All vehicles whose license plate ends in 86 (License Plate Number column).

The SQL Server LIKE operator is used to perform a wildcard searches and retrieve rows that match a certain character pattern.

SELECT ..
FROM ..
WHERE  column_name  LIKE 'pattern'

SQL Server Wildcards Operators

Two symbols can be used to construct the search string:

% – The percent (%) sign, represents any sequence of characters (0 or more).

_ – The underscore (_) sign, represents any single character.

The following SQL Server examples demonstrate the usage of these symbols:

SELECT last_name
FROM employees
WHERE last_name LIKE '%n'

• ‘%n’ – all of the last names that end in the letter ‘n’. This SQL Server LIKE example actually puts no lower or upper limit on the number of letters before the last letter – ‘n’ (any number of characters is allowed), but requires the string to end in the letter ‘n’.
• Values that meet this condition are, for example: ‘Georgeson’, ‘Harrison’, ‘Clapton’.
• Theoretically, an employee whose last name is : ‘n’ would have also been included here, because % represents any number of characters, including 0.

SELECT last_name
FROM employees
WHERE  last_name LIKE 'N%'

• ‘N%’ – all of the names that begin in the letter ‘N’. This SQL Sever SELECT statement actually puts no lower or upper limit on the number of letters after the first letter (any number of characters is allowed), but requires the word to begin with the letter ‘N’.
• Values that meet this condition are, for example: ‘Nelson’, ‘Newton’, ‘Nash’.
• String values in SQL Server are not case sensitive (by default); therefore, an employee whose last name is ‘norman’ would also have been included here.
• Theoretically, an employee whose last name is  ‘n’ would have also been included here, because % represents any number of characters, including 0.

SELECT last_name
FROM employees
WHERE  last_name LIKE '%n%'

• ‘%n%’ – all of the names that contain the letter ‘n’. This SQL Server LIKE example actually puts no lower or upper limit on the number of letters before or after the letter ‘n’ (any number of characters is allowed), but requires the word to contain the letter ‘n’.
• Values that meet this condition are, for example: ‘Lennon, ‘Ono’, ‘Hendrix’, ‘Nash’, ‘Clapton’.
• Theoretically, an employee whose name is  ‘n’ would have also been included here, because % represents any number of characters, including 0.

SELECT last_name
FROM employees
WHERE  last_name LIKE '_n%'

• ‘_n%’ – all of the names whose second letter is ‘n’.
• This SQL SELECT statement allows any single character to appear as the first character of the name ( _ stands for a single character). The second character of the name must be equal to ‘n’. After the letter ‘n’, there is no lower or upper limit to the letters that can follow.
• Values that meet this condition are, for example: ‘Ono’, ‘Andrews’ and ‘Antony’.

SQL Server NOT Operator

This SQL Server operator produce opposite results than those produced by the LIKE operator.

The NOT keyword is written before the LIKE keyword.

This SQL Server example would retrieve all employees whose last name contains the letter ‘n’ :

SELECT last_name
FROM employees
WHERE  last_name LIKE '%n%'

This SQL Server LIKE example would retrieve all employees whose last name does not contain the letter ‘n’ :

SELECT last_name
FROM employees
WHERE  last_name NOT LIKE '%n%'