SQL LIKE Operator

Until now you have been using operators such as conditional operators (for testing equality), BETWEEN operator (for checking against a range of values) or IN operator (for checking against a set of values) in which one thing is common; They all search for an exact match.

Filtering data that way is all well and good. But sometimes a situation can arise when you want to search for partial string matches, for example, you want to search for all products that include a text ‘Chocolate Cookie’ within their name. You cannot achieve this with simple comparison operators; This is where you need a wildcard search.

Wildcards are special characters with which you can create search patterns that can be compared against your data.

The LIKE Operator

To use wildcards in search clauses, the LIKE operator is used. LIKE instructs the DBMS to search using a wildcard match rather than a straight equality match.

The LIKE operator has the following syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE search_pattern;

The search_pattern is a sequence of characters to search for in the column. It is made up of literal text, wildcard characters, or a combination of both.

The Following wildcard characters are used to create a search pattern:

Wildcard characterDescription
%The percent sign matches any string of zero or more characters.
_The underscore matches a single character.

Let’s take a look at them one by one.

Sample Table

To help you better understand the examples, and enable you to follow along with the tutorial, we are going to use the following sample table.

This table is part of an ‘Product Management System’ used by an imaginary distributor of cookies.

IDNamePrice ($)
1Vanilla Cookies3.00
2Cashew Nut Cookies3.00
3Chocolate Cookies4.00
4Chocolate Orange Cookies5.00
5White Chocolate Cookies6.00
6Dark Chocolate Chip Cookies6.00
7Double Chocolate Chip Cookies7.00
84 Ct. Cookies Variety Pack6.00
98 Ct. Cookies Variety Pack9.00
1012 Ct. Cookies Variety Pack12.00

The Percent Sign % Wildcard

The percent sign % is the most commonly used wildcard. % Matches zero, one or more characters at a specified location within a search string. For example, if you wanted to search for all products starting with the word ‘Chocolate’, you could write a query like:

SELECT *
FROM Products
WHERE Name LIKE 'Chocolate%';
IDNamePrice ($)
3Chocolate Cookies4.00
4Chocolate Orange Cookies5.00

In this example the search pattern Chocolate% tells the DBMS to match any value starting with the text ‘Chocolate’, regardless of any character after it.

You can use several wildcards to create a search pattern. The following example uses two wildcards, one at either end:

SELECT *
FROM Products
WHERE Name LIKE '%Chocolate%';
IDNamePrice ($)
3Chocolate Cookies4.00
4Chocolate Orange Cookies5.00
5White Chocolate Cookies6.00
6Dark Chocolate Chip Cookies6.00
7Double Chocolate Chip Cookies7.00

The above example uses the search pattern %Chocolate% which matches any value that contains the text ‘Chocolate’ anywhere within it, regardless of any characters before or after that text. It is important to note that % also matches zero characters; that’s why the result-set also includes products whose name starts with ‘Chocolate’.

Wildcards can be used anywhere within the search pattern, including in the middle, although this is rarely useful. The following example finds all products that start with a ‘D’ and end with ‘Cookies’.

SELECT *
FROM Products
WHERE Name LIKE 'D%Cookies';
IDNamePrice ($)
6Dark Chocolate Chip Cookies6.00
7Double Chocolate Chip Cookies7.00

The Underscore _ Wildcard

Like the percent sign %, the underscore _ is another wildcard character that is used extensively. The underscore is used the same way as the %, but the underscore matches only one character, rather than matching multiple characters.

Below example specifies the search pattern containing a single underscore wildcard followed by literal text.

SELECT *
FROM Products
WHERE Name LIKE '_ Ct. Cookies Variety Pack';
IDNamePrice ($)
84 Ct. Cookies Variety Pack6.00
98 Ct. Cookies Variety Pack9.00

As you can see only two rows are retrieved as the underscore matches 4 in the first row and 8 in the second row. But the ’12 Ct. Cookies Variety Pack’ did not match because the search pattern required only one wildcard match, not two.

More Examples

Below table shows some more search expressions and their interpretation.

Search expressionInterpretation
B%Finds any values that start with ‘B’
%tFinds any values that ends with ‘t’
%bas%Finds any values that have ‘bas’ in any position
_ir%Finds any values that have ‘ir’ in the second and third positions
A__%Finds any values that start with ‘A’ and are at least three characters in length
%0Finds any values that end with ‘0’
_c%1Finds any values that have a ‘c’ in the second position and end with a ‘1’
2___3Finds any values in a five-digit number that start with ‘2’ and end with ‘3’
a%oFinds any values that start with ‘a’ and ends with ‘o’
__t_Finds any four-character string with a ‘t’ in the third position
___-__-____Finds any 11-character string with dashes in the fourth and seventh positions

The NOT LIKE Operator

The LIKE operator is negated as NOT LIKE. When NOT LIKE is used, only values that are not similar are returned. For example, if you wanted to search for all products that do not have the word ‘chocolate’ anywhere in their name, you could write a query like:

SELECT *
FROM Products
WHERE Name NOT LIKE '%Chocolate%';
IDNamePrice ($)
1Vanilla Cookies3.00
2Cashew Nut Cookies3.00
84 Ct. Cookies Variety Pack6.00
98 Ct. Cookies Variety Pack9.00
1012 Ct. Cookies Variety Pack12.00