Explanation of the SQL LIKE operator and wildcards (contains examples)

The LIKE operator is used in the WHERE clause of SQL queries. It's a keyword used to perform a search according to a specific pattern in a column.

For example, you can search for records whose value in a column begins with a particular letter.

The sort of patterns are numerous and are tackled later on in the post.

1. Syntax

The syntax to use the LIKE operator is as follows:

SELECT column1, column2, ... 
FROM table_name
WHERE columnN LIKE pattern;

In this example the pattern has not been defined, we generally define those with a SQL wildcard.

2. Defining patterns with wildcards

There are two main types of wildcards often used with the LIKE operator:

• Percentage sign character - %
• Underscore character - _

> Note: MS Access uses an asterisk and a question mark ? In place of the percentage and underscore signs.

2.1 Percentage sign %

The character "%" is a wild card that replaces all other characters

WHERE EmployeeName LIKE 'a%'

Return records where EmployeeName starts with an “a”.

WHERE EmployeeName LIKE ' %a'

Return records where EmployeeName ends with an “a”.

WHERE EmployeeName LIKE '%or%'

Return records where EmployeeName has "or" in any position

WHERE EmployeeName LIKE 'pa%on'

This pattern allows you to find strings that start with “pa” and end with “on”, like “pants” or “pardon”.

2.2 Underscore _

The underscore wildcard is generally used less than the percentage sign, the character _ can be replaced by any character, but only one character. On the other hand, the percentage symbol % can be replaced by an incalculable number of characters.

WHERE EmployeeName LIKE ‘a_c’

Returns records where EmployeeName is “aac”, “abc” or even “azc”, etc..

WHERE EmployeeName LIKE '_a%'

Returns records where EmployeeName has an "a" in it’s second character position

WHERE EmployeeName LIKE 'a__%'

Returns records where EmployeeName starts with "a" and are at least 3 characters in length

3. Real life examples

Let's imagine we have a Customer table that contains the following user records

Let's say we want to get only customers from cities that start with an “N”, you can use the following query:

SELECT * FROM Customer WHERE City LIKE 'N%

With this query, only the following records will be returned: