What is the LIKE Operator?

The LIKE operator is used with the WHERE clause to search for a specified pattern in a column. It is especially useful when you do not know the exact value you want to find.

SQLite supports the LIKE operator and commonly uses two wildcard characters: % and _.

Basic LIKE Syntax

The basic syntax places the column name before LIKE and the search pattern after it.

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

The % Wildcard

The percent sign (%) represents zero or more characters. It can be placed at the beginning, end, or both sides of a search pattern.

Starts With a Pattern

To find names that start with the letter A, place the percent wildcard after the letter.

SELECT name
FROM employees
WHERE name LIKE 'A%';

Ends With a Pattern

To find names that end with the letter n, place the percent wildcard before the letter.

SELECT name
FROM employees
WHERE name LIKE '%n';

Contains a Pattern

To find names containing a specific sequence of characters, place % on both sides of the search pattern.

SELECT name
FROM employees
WHERE name LIKE '%an%';

The _ Wildcard

The underscore (_) wildcard represents exactly one character.

SELECT name
FROM employees
WHERE name LIKE 'J_n';

Using Multiple _ Wildcards

Multiple underscore characters can be used when you want to match a specific number of unknown characters.

SELECT name
FROM employees
WHERE name LIKE 'A___';

LIKE with Numbers

The LIKE operator can also be used with text representations of numeric values. For example, you can search for employee IDs beginning with a particular number.

SELECT employee_id, name
FROM employees
WHERE employee_id LIKE '10%';

LIKE with WHERE

The LIKE operator is normally used with WHERE to return only the rows that match a particular pattern.

SELECT name, department
FROM employees
WHERE department LIKE 'S%';

LIKE with AND

You can combine LIKE with other conditions using the AND operator.

SELECT name, salary
FROM employees
WHERE name LIKE 'A%'
AND salary > 60000;

LIKE with OR

The OR operator allows you to search for more than one pattern.

SELECT name, department
FROM employees
WHERE name LIKE 'A%'
OR name LIKE 'B%';

NOT LIKE

The NOT LIKE operator returns rows that do not match the specified pattern.

SELECT name, department
FROM employees
WHERE name NOT LIKE 'A%';

LIKE with ORDER BY

You can combine LIKE with ORDER BY to sort matching records.

SELECT name, salary
FROM employees
WHERE name LIKE 'A%'
ORDER BY salary DESC;

LIKE with LIMIT

SQLite allows you to combine LIKE with LIMIT when you want to return only a specific number of matching records.

SELECT name, department
FROM employees
WHERE name LIKE '%a%'
LIMIT 5;

Case Sensitivity in SQLite

In SQLite, the behavior of LIKE for ASCII characters is generally case-insensitive by default. For example, searching for 'a%' can match names beginning with both uppercase and lowercase A.

SELECT name
FROM employees
WHERE name LIKE 'a%';

LIKE Wildcards

Common LIKE Patterns

Advantages of LIKE

• Makes it easy to search for text patterns.
• Useful when the exact value is unknown.
• Supports flexible searches using wildcards.
• Can be combined with WHERE, AND, OR, ORDER BY, and LIMIT.
• Works directly with SQLite browser-based SQL execution.

Best Practices

• Use % when matching zero or more characters.
• Use _ when matching exactly one character.
• Use WHERE with LIKE to limit the search to relevant rows.
• Use ORDER BY when the matching results need to be sorted.
• Use LIMIT when only a small number of matching records are required.