Using SQL LIKE Operator to Query Data Based On Patterns

Summary: in this tutorial, we will show you how to use the SQL LIKE operator to query data based on patterns.

Introduction to SQL LIKE operator

The LIKEoperator allows you to search for a string of text based on a specified pattern. You can use the LIKEoperator in the WHERE clause of any valid SQL statement, such as SELECT, UPDATE, or DELETE.

SQL provides two wildcard characters that allow you to construct patterns. These two wildcards are percentage (%) and underscore (_).

Percentage (% ) wildcard matches a sequence of any character including space.
Underscore ( _ ) wildcard matches any single character.

The syntax of the LIKEoperator when using the SELECT statement is as follows:

SELECT 
    column1, column2
FROM
    table
WHERE
    column LIKE pattern;Code language: SQL (Structured Query Language) (sql)

The data type of the column in the WHEREclause must be alphanumeric, e.g., char, varchar, etc., to use the LIKE operator.

SQL LIKE operator examples

Let’s take a look at some examples of using the LIKEoperator.

SQL LIKE operator with percentage wildcard (%) examples

Suppose you want to find an employee whose last name starts with the letter D, you can use the following query.

SELECT 
    lastname, firstname
FROM
    employees
WHERE
    lastname LIKE 'D%'Code language: SQL (Structured Query Language) (sql)

The pattern ‘D%’ matches any string that starts with character ‘D’ and is followed by any characters.

To find employees whose first name ends with ‘t’, you can execute the following query:

SELECT 
    lastname, firstname
FROM
    employees
WHERE
    firstname LIKE '%t'Code language: SQL (Structured Query Language) (sql)

The pattern ‘%t’ matches any string that ends with character ‘t’.

You can put the wildcard ‘%’ at the beginning and the end of a string to match any string that contains the string within the wildcards. For example, to find employees whose last names contain a string 'll‘, you can use the following query:

SELECT 
    lastname, firstname
FROM
    employees
WHERE
    lastname LIKE '%ll%'Code language: SQL (Structured Query Language) (sql)

Combination of the two wildcards example

You can combine two wildcard characters ‘%’ and ‘_’ to construct a pattern. For example, you can find an employee whose last name starts with any single character, followed by character a, and ends with any characters as the following query:

SELECT 
    lastname, firstname
FROM
    employees
WHERE
    lastname LIKE '_a%'Code language: SQL (Structured Query Language) (sql)

SQL LIKE operator with NOT operator

You can combine the LIKEoperator with the NOToperator to find any string that does not match a specified pattern. Suppose, you want to find employees whose first name does not start with the character ‘D’, you can perform the following query:

SELECT 
    lastname, firstname
FROM
    employees
WHERE
    lastname NOT LIKE 'D%'Code language: SQL (Structured Query Language) (sql)

Escape wildcard characters

In case the pattern you want to match contains the wildcard characters, e.g., 5% or _10. You need to escape the wildcard characters in the pattern to use this pattern.

Different database products have different ways to escape the wildcard characters ( %, _) in the pattern. The most common ways to escape the wildcard characters are to use the backslash (\) character or an ESCAPE clause.

The following example illustrates how to escape a wildcard character by using the backslash (\) character:

column LIKE '%\_10%'Code language: SQL (Structured Query Language) (sql)

The ESCAPEclause allows you to specify an escape character of your choice rather than the backslash character. For example, the following example uses @as the escape character:

column LIKE '%20@%' ESCAPE '@';Code language: SQL (Structured Query Language) (sql)

The pattern %20@% matches any string that ends with 20%.

In this tutorial, you have learned how to use the LIKE operator to find a string of text that matches a pattern. You’ve also learned how to use the wildcard characters percentage (%) and underscore (_) to construct patterns to use with the LIKE operator.