SQLite: LIKE Condition

This SQLite post explains how to use the SQLite LIKE condition to perform pattern matching with syntax and examples.

Description

The SQLite 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 SQLite is:

expression LIKE pattern

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

Example - Using % wildcard (percent sign wildcard)

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

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

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

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

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

In this SQLite LIKE condition example, we are looking for all employees whose last_name contains the letter 'e'.

Example - Using _ wildcard (underscore wildcard)

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

For example:

SELECT last_name, first_name
FROM employees
WHERE last_name LIKE 'H_nt';

This SQLite LIKE condition example would return all employees whose last_name is 4 characters long, where the first character is 'H' and the last two characters are 'nt'. For example, it could return last_name values such as: 'Hant', 'Hent', 'Hint', 'Hont', 'Hunt', etc.

Example - Using NOT Operator

Next, let's look at how to use the NOT Operator with wildcards.

Let's use the % wilcard with the NOT Operator. You could also use the SQLite LIKE condition to find employees whose department does not start with 'Acc'.

For example:

SELECT employee_id, last_name
FROM employees
WHERE department NOT LIKE 'Acc%';

By placing the NOT Operator in front of the SQLite LIKE condition, you are able to retrieve all employees whose department does not start with 'Acc'.