SQL Reference ยท Operators and Predicates
SQL LIKE operator
Reference for the SQL LIKE operator: the % and _ wildcards, NOT LIKE, ESCAPE, case sensitivity in each database, NULL handling and index performance.
LIKE compares text against a pattern. The pattern is ordinary text plus two wildcards: % matches any run of characters (including none) and _ matches exactly one character. It is part of standard SQL and works in every database.
The whole value must match the whole pattern. So LIKE 'Pen' behaves like = 'Pen', while LIKE 'Pen%' means "starts with Pen" and LIKE '%pen%' means "contains pen".
SELECT name FROM products WHERE name LIKE '%Speaker%';
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
expression [NOT] LIKE pattern [ESCAPE escape_char]
| Part | Description |
|---|---|
expression |
The text to test. |
pattern |
A string containing literal characters, % (zero or more characters) and _ (exactly one character). |
ESCAPE escape_char |
Optional single character. Put it before %, _ or itself in the pattern to match that character literally. |
Returns: true, false or NULL. If either operand is NULL, the result is NULL, so NOT LIKE does not return those rows either.
Dialect differences
| Behavior | SQLite | PostgreSQL | MySQL | SQL Server | Oracle |
|---|---|---|---|---|---|
| Case sensitivity | Insensitive for ASCII letters only | Sensitive | Depends on the column collation (usually insensitive) | Depends on the collation (usually insensitive) | Sensitive |
| Case-insensitive variant | Default | ILIKE |
Default collation | Default collation | LOWER(x) LIKE LOWER(p) |
| Default escape character | None (use ESCAPE) |
Backslash | Backslash | None | None |
| Extra wildcards | None | None | None | [abc], [a-f], [^abc] |
None |
-- PostgreSQL: case-insensitive match SELECT name FROM products WHERE name ILIKE '%speaker%'; -- SQL Server: character classes in the pattern SELECT name FROM products WHERE name LIKE '[CD]%';
Usage notes
- Always set
ESCAPEfor portable code. In SQLite and SQL Server a backslash is an ordinary character unless you declare it withESCAPE '\'. Without it you cannot match a literal%or_. - A leading
%is slow.LIKE 'abc%'can use an index range on the column when the collation allows it (PostgreSQL needs thetext_pattern_opsoperator class outside the C locale; SQLite needs a matching collation).LIKE '%abc'and'%abc%'scan every row. For real text search use full-text indexes (FTS5 in SQLite). - Wrapping the column in a function such as
LOWER(name) LIKE ...prevents index use unless you index that expression. - Case in SQLite: only ASCII letters are folded, so
'ร' LIKE 'รค'is false.GLOBis the case-sensitive alternative: seeGLOB. - Trailing spaces in
CHARcolumns can make a pattern fail in some databases. Trim or useVARCHAR. - Never build patterns by gluing user input into the SQL string. Pass the pattern as a parameter, and escape any
%or_the user typed if they should match literally. - Regular expressions are more powerful but not portable: see regular expression matching.