QuerydrillLIKE
LIKE compares text against a pattern where % matches any sequence of characters (including none) and _ matches exactly one: name LIKE 'A%' finds names starting with A, LIKE '%son' names ending in son, LIKE '%mar%' names containing mar. PostgreSQL LIKE is case sensitive (ILIKE is not); SQLite LIKE ignores case for ASCII letters.
Syntax
WHERE column LIKE 'pattern' [ESCAPE '\']| Part | What it is |
|---|---|
% | Any number of characters, including zero |
_ | Exactly one character |
ESCAPE | Declares a character that makes a following % or _ literal |
Examples
SELECT name FROM customers WHERE name LIKE 'Ma%';Result: Maria, Marco, Matteo
Starts with Ma. In PostgreSQL 'ma%' would not match Maria; use ILIKE there.
SELECT email FROM customers WHERE email LIKE '%@gmail.com';Result: All Gmail addresses
SELECT code FROM products WHERE code LIKE 'A_1%';Result: A01, A11-X, AB1
_ matches exactly one character between A and 1.
Common mistakes
- LIKE 'Paris' without any wildcard behaves like = and does not find 'Paris, France'; add % where the rest of the text can vary.
- In PostgreSQL LIKE 'paris%' misses 'Paris' because it is case sensitive; use ILIKE or LOWER(city) LIKE 'paris%'.
- A leading % (LIKE '%abc') cannot use a normal index, so it is slow on big tables; prefer a prefix pattern or full-text search when possible.
Practice LIKE
These drills check your answer on live data, free, no account needed.
- Pattern matchReturn the names of products whose name contains the letter o, in upper or lower case.
Questions
Is SQL LIKE case sensitive?
It depends on the database. PostgreSQL: yes (use ILIKE for case insensitive). SQLite: no for ASCII letters by default. MySQL: no with the default collation.
How do I match a string that contains a word?
Put % on both sides: WHERE description LIKE '%refund%'. For a case-insensitive version wrap both sides in LOWER().
Related
- WHEREUse WHERE to keep only the rows that match a condition: equals, greater than, text matches, NULL checks, and combinations with AND, OR and NOT.
- String functionsThe string functions you use in SQLite and PostgreSQL: || for concatenation, UPPER and LOWER, LENGTH, SUBSTR, TRIM, REPLACE, INSTR and POSITION, with notes.
- INWHERE column IN (...) keeps rows whose value appears in a list or in a subquery result. Examples, NOT IN, the NULL trap, and when to use EXISTS instead.
- AND, OR, NOTHow to combine WHERE conditions with AND, OR and NOT, why AND binds before OR, how parentheses fix it, and how NULL changes the result of a logical test.