Skip to content
Tutorials Center
My learning

Topics

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".

SQL
SELECT name FROM products WHERE name LIKE '%Speaker%';

Try it

SQL Contains, starts with, ends with, one character ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Case and ESCAPE

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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
SQL
-- 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 ESCAPE for portable code. In SQLite and SQL Server a backslash is an ordinary character unless you declare it with ESCAPE '\'. 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 the text_pattern_ops operator 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. GLOB is the case-sensitive alternative: see GLOB.
  • Trailing spaces in CHAR columns can make a pattern fail in some databases. Trim or use VARCHAR.
  • 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.