SQL Server: LIKE Condition

In this post explains how to use the LIKE condition in SQL Server (Transact-SQL) to perform pattern matching with syntax and examples.

Description

The SQL Server (Transact-SQL) LIKE condition allows wildcards to be used in the WHERE clause of a SELECT, INSERT, UPDATE, or DELETE statement. This allows you to perform pattern matching.

Syntax

The syntax for the LIKE condition in SQL Server (Transact-SQL) is:

expression LIKE pattern [ ESCAPE 'escape_character' ]

Parameters or Arguments

expression

A character expression such as a column or field.

pattern

A character expression that contains pattern matching. The patterns that you can choose from are:

Wildcard	Explanation
%	Allows you to match any string of any length (including zero length)
_	Allows you to match on a single character
[ ]	Allows you to match on any character in the [ ] brackets (for example, [abc] would match on a, b, or c characters)
[^]	Allows you to match on any character not in the [^] brackets (for example, [^abc] would match on any character that is not a, b, or c characters)

escape_character

Optional. It allows you to test for literal instances of a wildcard character such as % or _.

Example - Using % wildcard (percent sign wildcard)

The first SQL Server LIKE example that we will look at involves using the % wildcard (percent sign wildcard).

Let's explain how the % wildcard works in the SQL Server LIKE condition. We want to find all of the employees whose last_name begins with 'B'.

For example:

SELECT *
FROM employees
WHERE last_name LIKE 'B%';

You can also using the % wildcard multiple times within the same string. For example,

SELECT *
FROM employees
WHERE last_name LIKE '%o%';

In this SQL Server LIKE condition example, we are looking for all employees whose last_name contains the letter 'o'.

Example - Using _ wildcard (underscore wildcard)

Next, let's explain how the _ wildcard (underscore wildcard) works in the SQL Server LIKE condition. Remember that _ wildcard is looking for only one character.

For example:

SELECT *
FROM employees
WHERE first_name LIKE 'Ad_m';

This SQL Server LIKE condition example would return all employees whose first_name is 4 characters long, where the first two characters is 'Ad' and the last character is 'm'. For example, it could return employees whose first_name is 'Adam', 'Adem', 'Adim', 'Adom', 'Adum', etc.

Here is another example:

SELECT *
FROM employees
WHERE employee_number LIKE '123_';

You might find that you are looking for an employee_number, but you only have 3 of the 4 digits. The example above, would retrieve potentially 10 records back (where the missing value could equal anything from 0 to 9). For example, it could return employees whose employee numbers are:

1230, 1231, 1232, 1233, 1234, 1235, 1236, 1237, 1238, 1239

Example - Using [ ] wildcard (square brackets wildcard)

Next, let's explain how the [ ] wildcard (square brackets wildcard) works in the SQL Server LIKE condition. Remember that what is contained within the square brackets are characters that you are trying to match on.

For example:

SELECT *
FROM employees
WHERE first_name LIKE 'Sm[iy]th';

This SQL Server LIKE condition example would return all employees whose first_name is 5 characters long, where the first two characters is 'Sm' and the last two characters is 'th', and the third character is either 'i' or 'y'. So in this case, it would match on either 'Smith' or 'Smyth'.

Example - Using [^] wildcard (square brackets with ^ wildcard)

Next, let's explain how the [^] wildcard (square brackets with ^ wildcard) works in the SQL Server LIKE condition. Remember that what is contained within the square brackets are characters that you do NOT want to match on.

For example:

SELECT *
FROM employees
WHERE first_name LIKE 'Sm[^iy]th';

This SQL Server LIKE condition example would return all employees whose first_name is 5 characters long, where the first two characters is 'Sm' and the last two characters is 'th', and the third character is neither 'i' or 'y'. So in this case, it would match on values such as 'Smath', 'Smeth', 'Smoth', etc. But it would not match on either 'Smith' or 'Smyth'.

Example - Using NOT Operator

Next, let's look at how you would use the SQL Server NOT Operator with wildcards.

Let's use the % wilcard with the NOT Operator. You could also use the SQL Server LIKE condition to find employees whose last_name does not start with 'B'.

For example:

SELECT *
FROM employees
WHERE last_name NOT LIKE 'B%';

By placing the NOT Operator in front of the SQL Server LIKE condition, you are able to retrieve all employees whose last_name does not start with 'B'.

Example - Using Escape Characters

It is important to understand how to "Escape Characters" when pattern matching. These examples deal specifically with escaping characters in SQL Server.

Let's say you wanted to search for a % or a _ character in the SQL Server LIKE condition. You can do this using an Escape character.

Please note that you can only define an escape character as a single character (length of 1).

For example:

SELECT *
FROM employees
WHERE secret_hint LIKE '123!%455' ESCAPE '!';

This SQL Server LIKE condition example identifies the ! character as an escape character. This statement will return all employees whose secret_hint is 123%455.

Here is another more complicated example using escape characters in the SQL Server LIKE condition.

SELECT *
FROM employees
WHERE secret_hint LIKE 'H%!%' ESCAPE '!';

This SQL Server LIKE condition example returns all employees whose secret_hint starts with H and ends in %. For example, it would return a value such as 'Help%'.

You can also use the escape character with the _ character in the SQL Server LIKE condition.

For example:

SELECT *
FROM employees
WHERE secret_hint LIKE 'H%!_' ESCAPE '!';

This SQL Server LIKE condition example returns all employees whose secret_hint starts with H and ends in _. For example, it would return a value such as 'Help_'.