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.
Syntax
SELECT name
FROM products
WHERE name LIKE '%desk%'name LIKE '...'- The column to test, then the pattern as text in single quotes.
NOT LIKEkeeps 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.
| id | name | sku | price |
|---|---|---|---|
| 1 | Oak Desk | DSK-100 | 240 |
| 2 | Desk Lamp | LMP-210 | 35 |
| 3 | Standing Desk | DSK-250 | 420 |
| 4 | desk pad | PAD_015 | 18 |
| 5 | Office Chair | CHR-300 | 120 |
| 6 | Chair Mat | MAT-120 | 45 |
| 7 | Monitor Arm | ARM-07 | 60 |
| 8 | LED Lamp | LMP-33 | 28 |
Table: products
| id | name | sku | price |
|---|---|---|---|
| 1 | Oak Desk | DSK-100 | 240 |
| 2 | Desk Lamp | LMP-210 | 35 |
| 3 | Standing Desk | DSK-250 | 420 |
| 4 | desk pad | PAD_015 | 18 |
| 5 | Office Chair | CHR-300 | 120 |
| 6 | Chair Mat | MAT-120 | 45 |
| 7 | Monitor Arm | ARM-07 | 60 |
| 8 | LED Lamp | LMP-33 | 28 |
| name |
|---|
| Desk Lamp |
| desk pad |
LIKE ignores case for ASCII letters. Oak Desk and Standing Desk contain the word but don't start with it. Move the % to the front, '%desk', and you get those two instead: ends with.Table: products
| id | name | sku | price |
|---|---|---|---|
| 1 | Oak Desk | DSK-100 | 240 |
| 2 | Desk Lamp | LMP-210 | 35 |
| 3 | Standing Desk | DSK-250 | 420 |
| 4 | desk pad | PAD_015 | 18 |
| 5 | Office Chair | CHR-300 | 120 |
| 6 | Chair Mat | MAT-120 | 45 |
| 7 | Monitor Arm | ARM-07 | 60 |
| 8 | LED Lamp | LMP-33 | 28 |
| name |
|---|
| Oak Desk |
| Desk Lamp |
| Standing Desk |
| desk pad |
% can also match nothing, so a name that starts or ends with the word still counts. With no % at all, LIKE 'desk' returns no rows here: no product is called just that.The underscore: exactly one character
Use _ when the length matters: a code with three digits, a five-letter word, a date with any day.
Table: products
| id | name | sku | price |
|---|---|---|---|
| 1 | Oak Desk | DSK-100 | 240 |
| 2 | Desk Lamp | LMP-210 | 35 |
| 3 | Standing Desk | DSK-250 | 420 |
| 4 | desk pad | PAD_015 | 18 |
| 5 | Office Chair | CHR-300 | 120 |
| 6 | Chair Mat | MAT-120 | 45 |
| 7 | Monitor Arm | ARM-07 | 60 |
| 8 | LED Lamp | LMP-33 | 28 |
| name | sku |
|---|---|
| Desk Lamp | LMP-210 |
LMP-210 matches and LMP-33 doesn't, because it has only two characters after the dash. With 'LMP-%' both lamps come back.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:
Table: products
| id | name | sku | price |
|---|---|---|---|
| 1 | Oak Desk | DSK-100 | 240 |
| 2 | Desk Lamp | LMP-210 | 35 |
| 3 | Standing Desk | DSK-250 | 420 |
| 4 | desk pad | PAD_015 | 18 |
| 5 | Office Chair | CHR-300 | 120 |
| 6 | Chair Mat | MAT-120 | 45 |
| 7 | Monitor Arm | ARM-07 | 60 |
| 8 | LED Lamp | LMP-33 | 28 |
| name | sku |
|---|---|
| Oak Desk | DSK-100 |
| Desk Lamp | LMP-210 |
| Standing Desk | DSK-250 |
| desk pad | PAD_015 |
| Office Chair | CHR-300 |
| Chair Mat | MAT-120 |
| Monitor Arm | ARM-07 |
| LED Lamp | LMP-33 |
_ is the wildcard, so the pattern only asks for a code with at least one character. To mean the character itself, name an escape character and put it in front: sku LIKE '%!_%' ESCAPE '!' returns only PAD_015. The same goes for a literal %.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:
Table: products
| id | name | sku | price |
|---|---|---|---|
| 1 | Oak Desk | DSK-100 | 240 |
| 2 | Desk Lamp | LMP-210 | 35 |
| 3 | Standing Desk | DSK-250 | 420 |
| 4 | desk pad | PAD_015 | 18 |
| 5 | Office Chair | CHR-300 | 120 |
| 6 | Chair Mat | MAT-120 | 45 |
| 7 | Monitor Arm | ARM-07 | 60 |
| 8 | LED Lamp | LMP-33 | 28 |
| No rows |
%lamp%. Write one LIKE per pattern and join them with OR: WHERE name LIKE '%lamp%' OR name LIKE '%chair%' returns four products.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.
Table: products
| id | name | sku | price |
|---|---|---|---|
| 1 | Oak Desk | DSK-100 | 240 |
| 2 | Desk Lamp | LMP-210 | 35 |
| 3 | Standing Desk | DSK-250 | 420 |
| 4 | desk pad | PAD_015 | 18 |
| 5 | Office Chair | CHR-300 | 120 |
| 6 | Chair Mat | MAT-120 | 45 |
| 7 | Monitor Arm | ARM-07 | 60 |
| 8 | LED Lamp | LMP-33 | 28 |
| name |
|---|
| Office Chair |
| Chair Mat |
| Monitor Arm |
| LED Lamp |
NULL LIKE '%desk%' is NULL, and so is NULL NOT LIKE '%desk%'.Is LIKE case sensitive?
It depends on the database, so check before you trust a result.
- SQLite: its documentation says
LIKEignores case for ASCII characters by default, so'a' LIKE 'A'is true, but not for characters beyond ASCII:'æ' LIKE 'Æ'is false. The=operator is different:name = 'desk lamp'finds nothing in this table, where the name is stored as Desk Lamp. - PostgreSQL: its documentation gives
ILIKEas the keyword to use instead ofLIKEto make the match case-insensitive, and notes that it is a PostgreSQL extension, not standard SQL. SQLite has noILIKE: it is a syntax error there. - MySQL: its documentation shows
'abc' LIKE 'ABC'returning 1 and says string comparisons are not case-sensitive unless one of the operands has a case-sensitive collation or is a binary string.
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.
Return the name of every product whose name ends with Lamp.
Table: products
| id | name | sku | price |
|---|---|---|---|
| 1 | Oak Desk | DSK-100 | 240 |
| 2 | Desk Lamp | LMP-210 | 35 |
| 3 | Standing Desk | DSK-250 | 420 |
| 4 | desk pad | PAD_015 | 18 |
| 5 | Office Chair | CHR-300 | 120 |
| 6 | Chair Mat | MAT-120 | 45 |
| 7 | Monitor Arm | ARM-07 | 60 |
| 8 | LED Lamp | LMP-33 | 28 |
Show the answer
SELECT name
FROM products
WHERE name LIKE '%lamp'| 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.
Return the name and sku of the products whose sku starts with DSK- or with CHR-.
Table: products
| id | name | sku | price |
|---|---|---|---|
| 1 | Oak Desk | DSK-100 | 240 |
| 2 | Desk Lamp | LMP-210 | 35 |
| 3 | Standing Desk | DSK-250 | 420 |
| 4 | desk pad | PAD_015 | 18 |
| 5 | Office Chair | CHR-300 | 120 |
| 6 | Chair Mat | MAT-120 | 45 |
| 7 | Monitor Arm | ARM-07 | 60 |
| 8 | LED Lamp | LMP-33 | 28 |
Show the answer
SELECT name, sku
FROM products
WHERE sku LIKE 'DSK-%' OR sku LIKE 'CHR-%'| name | sku |
|---|---|
| Oak Desk | DSK-100 |
| Standing Desk | DSK-250 |
| Office Chair | CHR-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.
Return the name of every product that does not have desk anywhere in its name, in capitals or not.
Table: products
| id | name | sku | price |
|---|---|---|---|
| 1 | Oak Desk | DSK-100 | 240 |
| 2 | Desk Lamp | LMP-210 | 35 |
| 3 | Standing Desk | DSK-250 | 420 |
| 4 | desk pad | PAD_015 | 18 |
| 5 | Office Chair | CHR-300 | 120 |
| 6 | Chair Mat | MAT-120 | 45 |
| 7 | Monitor Arm | ARM-07 | 60 |
| 8 | LED Lamp | LMP-33 | 28 |
Show the answer
SELECT name
FROM products
WHERE name NOT LIKE '%desk%'| 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.
Return the sku of every product whose sku contains an underscore (_).
Table: products
| id | name | sku | price |
|---|---|---|---|
| 1 | Oak Desk | DSK-100 | 240 |
| 2 | Desk Lamp | LMP-210 | 35 |
| 3 | Standing Desk | DSK-250 | 420 |
| 4 | desk pad | PAD_015 | 18 |
| 5 | Office Chair | CHR-300 | 120 |
| 6 | Chair Mat | MAT-120 | 45 |
| 7 | Monitor Arm | ARM-07 | 60 |
| 8 | LED Lamp | LMP-33 | 28 |
Show the answer
SELECT sku
FROM products
WHERE sku LIKE '%!_%' ESCAPE '!'| 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.