SQL practice › SQL reference › LIKE

SQL LIKE: the % and _ wildcards, several patterns and case, with queries you can run

LIKE tests text against a pattern. In the pattern, % stands for any run of characters, including none, and _ for exactly one character. So name LIKE 'desk%' means "starts with desk", '%desk' means "ends with desk" and '%desk%' means "contains desk".

The pattern has to describe the whole value, and whether capitals matter depends on the database. In SQLite, which runs this page, LIKE ignores case for the letters A to Z. In PostgreSQL the case-insensitive form is a separate keyword, ILIKE.

Skip to the 4 exercises ↓

Syntax

SELECT name
FROM products
WHERE name LIKE '%desk%'
name LIKE '...'
The column to test, then the pattern as text in single quotes. NOT LIKE keeps the rows that don't match.
%
Any run of characters, of any length, including none.
_
Exactly one character, whatever it is.
desk
Every other character stands for itself.

The table

Eight products. Look at desk pad, written in lower case, and at its code PAD_015, the only one with an underscore.

products
idnameskuprice
1Oak DeskDSK-100240
2Desk LampLMP-21035
3Standing DeskDSK-250420
4desk padPAD_01518
5Office ChairCHR-300120
6Chair MatMAT-12045
7Monitor ArmARM-0760
8LED LampLMP-3328
Live queryStarts with
Table: products
products
idnameskuprice
1Oak DeskDSK-100240
2Desk LampLMP-21035
3Standing DeskDSK-250420
4desk padPAD_01518
5Office ChairCHR-300120
6Chair MatMAT-12045
7Monitor ArmARM-0760
8LED LampLMP-3328
Ctrl/⌘ + Enter to run
Result · 2 rows
name
Desk Lamp
desk pad
Live queryContains
Table: products
products
idnameskuprice
1Oak DeskDSK-100240
2Desk LampLMP-21035
3Standing DeskDSK-250420
4desk padPAD_01518
5Office ChairCHR-300120
6Chair MatMAT-12045
7Monitor ArmARM-0760
8LED LampLMP-3328
Ctrl/⌘ + Enter to run
Result · 4 rows
name
Oak Desk
Desk Lamp
Standing Desk
desk pad

The underscore: exactly one character

Use _ when the length matters: a code with three digits, a five-letter word, a date with any day.

Live queryOne underscore per character
Table: products
products
idnameskuprice
1Oak DeskDSK-100240
2Desk LampLMP-21035
3Standing DeskDSK-250420
4desk padPAD_01518
5Office ChairCHR-300120
6Chair MatMAT-12045
7Monitor ArmARM-0760
8LED LampLMP-3328
Ctrl/⌘ + Enter to run
Result · 1 row
namesku
Desk LampLMP-210

When the text itself contains _ or %

This one gives wrong rows without an error. You want the codes that contain an underscore, so you put an underscore in the pattern:

Live queryAn underscore that matches everything
Table: products
products
idnameskuprice
1Oak DeskDSK-100240
2Desk LampLMP-21035
3Standing DeskDSK-250420
4desk padPAD_01518
5Office ChairCHR-300120
6Chair MatMAT-12045
7Monitor ArmARM-0760
8LED LampLMP-3328
Ctrl/⌘ + Enter to run
Result · 8 rows
namesku
Oak DeskDSK-100
Desk LampLMP-210
Standing DeskDSK-250
desk padPAD_015
Office ChairCHR-300
Chair MatMAT-120
Monitor ArmARM-07
LED LampLMP-33

SQLite has no escape character until you add ESCAPE. PostgreSQL's and MySQL's documentation both give the backslash as their default escape character.

LIKE with multiple values

There is no LIKE IN (...). IN compares for equality, so a % inside it is just a percent sign:

Live queryWildcards inside IN: no error, no rows
Table: products
products
idnameskuprice
1Oak DeskDSK-100240
2Desk LampLMP-21035
3Standing DeskDSK-250420
4desk padPAD_01518
5Office ChairCHR-300120
6Chair MatMAT-12045
7Monitor ArmARM-0760
8LED LampLMP-3328
Ctrl/⌘ + Enter to run
Result · 0 rows
No rows

The other slip is to write the column only once: WHERE name LIKE '%lamp%' OR '%chair%'. SQLite runs it and returns the two lamps and no chair. What follows the OR is a piece of text, not a test.

NOT LIKE

NOT LIKE keeps the rows that don't match the pattern.

Live queryNames without desk
Table: products
products
idnameskuprice
1Oak DeskDSK-100240
2Desk LampLMP-21035
3Standing DeskDSK-250420
4desk padPAD_01518
5Office ChairCHR-300120
6Chair MatMAT-12045
7Monitor ArmARM-0760
8LED LampLMP-3328
Ctrl/⌘ + Enter to run
Result · 4 rows
name
Office Chair
Chair Mat
Monitor Arm
LED Lamp

Is LIKE case sensitive?

It depends on the database, so check before you trust a result.

To ignore case on purpose, whatever the default, lower both sides: LOWER(name) LIKE 'desk%'. In SQLite, LOWER converts ASCII letters only.

Practice: 4 exercises

Write the query in the box and press Run. Your result is compared with the right one, on this data and again on changed data, so a query with the values typed in won't pass. Column names don't matter; the columns and rows do.

Exercise 1Ends with

Return the name of every product whose name ends with Lamp.

Table: products
products
idnameskuprice
1Oak DeskDSK-100240
2Desk LampLMP-21035
3Standing DeskDSK-250420
4desk padPAD_01518
5Office ChairCHR-300120
6Chair MatMAT-12045
7Monitor ArmARM-0760
8LED LampLMP-3328
Ctrl/⌘ + Enter to run
Show the answer
SELECT name
FROM products
WHERE name LIKE '%lamp'
Result · 2 rows
name
Desk Lamp
LED Lamp

'%lamp' allows anything before the word and nothing after it. '%lamp%' returns the same two rows on this table, but it would also match a Lamp Shade.

Exercise 2Two patterns

Return the name and sku of the products whose sku starts with DSK- or with CHR-.

Table: products
products
idnameskuprice
1Oak DeskDSK-100240
2Desk LampLMP-21035
3Standing DeskDSK-250420
4desk padPAD_01518
5Office ChairCHR-300120
6Chair MatMAT-12045
7Monitor ArmARM-0760
8LED LampLMP-3328
Ctrl/⌘ + Enter to run
Show the answer
SELECT name, sku
FROM products
WHERE sku LIKE 'DSK-%' OR sku LIKE 'CHR-%'
Result · 3 rows
namesku
Oak DeskDSK-100
Standing DeskDSK-250
Office ChairCHR-300

One LIKE per pattern, joined with OR: two desks and the office chair. LIKE takes a single pattern, and IN doesn't understand wildcards.

Exercise 3Does not contain

Return the name of every product that does not have desk anywhere in its name, in capitals or not.

Table: products
products
idnameskuprice
1Oak DeskDSK-100240
2Desk LampLMP-21035
3Standing DeskDSK-250420
4desk padPAD_01518
5Office ChairCHR-300120
6Chair MatMAT-12045
7Monitor ArmARM-0760
8LED LampLMP-3328
Ctrl/⌘ + Enter to run
Show the answer
SELECT name
FROM products
WHERE name NOT LIKE '%desk%'
Result · 4 rows
name
Office Chair
Chair Mat
Monitor Arm
LED Lamp

'%desk%' matches the word anywhere, and NOT turns the test around. With 'desk%' only the names that start with it would be removed, and Oak Desk would stay.

Exercise 4A real underscore

Return the sku of every product whose sku contains an underscore (_).

Table: products
products
idnameskuprice
1Oak DeskDSK-100240
2Desk LampLMP-21035
3Standing DeskDSK-250420
4desk padPAD_01518
5Office ChairCHR-300120
6Chair MatMAT-12045
7Monitor ArmARM-0760
8LED LampLMP-3328
Ctrl/⌘ + Enter to run
Show the answer
SELECT sku
FROM products
WHERE sku LIKE '%!_%' ESCAPE '!'
Result · 1 row
sku
PAD_015

ESCAPE '!' makes ! the escape character, and !_ then means a real underscore. Any character that isn't in your data works as the escape. In SQLite, instr(sku, '_') > 0 finds the same row without LIKE.

Questions people ask

How do I use LIKE with multiple values?

Write one LIKE per pattern and join them with OR: WHERE name LIKE '%lamp%' OR name LIKE '%chair%'. There is no LIKE IN (...), and wildcards inside a plain IN list are compared as ordinary characters, so the query returns nothing instead of failing.

Is SQL LIKE case sensitive?

It depends on the database. SQLite's LIKE ignores case for ASCII letters by default. MySQL's documentation says the comparison is not case-sensitive unless a case-sensitive collation or a binary string is involved. In PostgreSQL the case-insensitive form is ILIKE. LOWER(name) LIKE 'desk%' ignores case whatever the default is.

What is the difference between LIKE and ILIKE?

ILIKE is PostgreSQL's case-insensitive LIKE. Its documentation says it matches without regard to case according to the active locale, and that it is a PostgreSQL extension, not part of the SQL standard. It doesn't exist in SQLite, where plain LIKE already ignores case for ASCII letters.

What is the difference between % and _ in LIKE?

% matches any number of characters, including none. _ matches exactly one. 'LMP-%' matches every code that starts with LMP-, and 'LMP-___' only the ones with exactly three characters after the dash.

How do I search for a literal % or _ with LIKE?

Name an escape character with ESCAPE and put it before the wildcard you mean literally: sku LIKE '%!_%' ESCAPE '!' finds the codes that contain an underscore. Without it, '%_%' matches every value that has at least one character.

Keep going

Lesson 1 is seven drills on an employees table: SELECT, WHERE, ORDER BY, LIMIT and LIKE. It's free, like lessons 2, 3 and 4 and the level test.

Related

SQL NULL: IS NULL, COALESCE and the NOT IN trapSQL CASE WHEN: conditions, ELSE and NULLSQL SELECT practiceSQL skills testSQL reference with queries you can runSQL practice online

Written and tested by Nico, who builds SheetDrill (about). Every query on this page was run on SQLite, and every exercise checked against right and wrong answers, before the page was published. Other databases differ in places; where they do, the page says so. Checked against: SQLite documentation: SQL Language Expressions (the LIKE operator), SQLite documentation: PRAGMA case_sensitive_like, SQLite documentation: Built-In Scalar SQL Functions (lower), PostgreSQL documentation: Pattern Matching (LIKE and ILIKE), MySQL 8.4 Reference Manual: String Comparison Functions and Operators. Last checked November 2026.