Using SQL LIKE Operator to Query Data Based On Patterns

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

Introduction to SQL LIKE operator

The LIKE operator allows you to search for a string of text based on a specified pattern. You can use the LIKE operator in the WHERE clause of any valid SQL statement such as SELECTUPDATE 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 LIKE operator when using with the SELECT statement is as follows:

SELECT column1, column2 FROM table WHERE column LIKE pattern;

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

SQL LIKE operator examples

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

SQL LIKE operator with percentage wildcard (%) examples

Suppose you want to find 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: JavaScript (javascript)
SQL LIKE example

The pattern ‘D%’ matches any string that starts with character ‘D’ and 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: JavaScript (javascript)
SQL LIKE percentage wildcard example

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 name contain string 'll‘, you can use the following query:

SELECT lastname, firstname FROM employees WHERE lastname LIKE '%ll%'
Code language: JavaScript (javascript)
SQL LIKE percentage wildcards

Combination of the two wildcards example

You can combine two wildcard characters ‘%’ and ‘_’ to construct a pattern. For example, you can find 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: JavaScript (javascript)
SQL LIKE wildcards combination

SQL LIKE operator with NOT operator

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

SELECT lastname, firstname FROM employees WHERE lastname NOT LIKE 'D%'
Code language: JavaScript (javascript)
SQL NOT LIKE example

Escape wildcard characters

In case the pattern that you want to match contains the wildcard characters e.g., 5% or _10. To use this pattern, you need to escape the wildcard characters in the 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: JavaScript (javascript)

The ESCAPE clause 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: JavaScript (javascript)

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

In this tutorial, you have learned how to use the LIKE operator to find string of text which 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.