LIKEQuery Techniques
Buy Me A Coffee

Pattern matching within strings using % (any sequence of characters) and _ (exactly one character) wildcards.

Pattern-matches strings using percent (any sequence) and underscore (any single character). Simple but limited -- it is case-sensitive and does not handle complex patterns efficiently. Use ESCAPE for literal percent or underscore characters. Not suitable for multi-character Unicode normalization.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported all Case sensitivity depends on column collation (case-insensitive by default with utf8mb4_general_ci). REGEXP / RLIKE for full regex.
PostgreSQL ✓ Supported all LIKE is case-sensitive. ILIKE for case-insensitive matching (PostgreSQL extension). SIMILAR TO for SQL-standard regex. ~ / ~* for POSIX regex matching.
SQL Server ✓ Supported all Case sensitivity depends on column/database collation. Uniquely supports [] character class ranges in patterns: LIKE '[A-Z]%'.
Oracle ✓ Supported all Case-sensitive by default. REGEXP_LIKE(col, pattern [, flags]) for full regex. ESCAPE clause for literal % or _ in patterns.
SQLite ✓ Supported all LIKE is case-insensitive for ASCII letters by default (unusual). GLOB provides case-sensitive matching using * and ? instead of % and _.

Details

SQLite's default case-insensitivity and PostgreSQL's case-sensitive LIKE (use ILIKE instead) are common porting gotchas. SQL Server's [] character class extension is unique among the five engines.

Standard Syntax

SELECT * FROM users WHERE name LIKE 'J%';

Version Support

MySQL: Since all PostgreSQL: Since all SQL Server: Since all Oracle: Since all SQLite: Since all

Per-Database Syntax & Notes

MySQL

Case sensitivity depends on column collation (case-insensitive by default with utf8mb4_general_ci). REGEXP / RLIKE for full regex.

SELECT * FROM tbl WHERE name LIKE 'J%'; SELECT * FROM tbl WHERE name REGEXP '^J[aeiou]';

PostgreSQL

LIKE is case-sensitive. ILIKE for case-insensitive matching (PostgreSQL extension). SIMILAR TO for SQL-standard regex. ~ / ~* for POSIX regex matching.

SELECT * FROM tbl WHERE name LIKE 'J%'; -- case-sensitive SELECT * FROM tbl WHERE name ILIKE 'j%'; -- case-insensitive SELECT * FROM tbl WHERE name ~ '^J[aeiou]'; -- POSIX regex

SQL Server

Case sensitivity depends on column/database collation. Uniquely supports [] character class ranges in patterns: LIKE '[A-Z]%'.

SELECT * FROM tbl WHERE name LIKE 'J%'; SELECT * FROM tbl WHERE name LIKE '[A-Z]%'; -- starts with any letter

Oracle

Case-sensitive by default. REGEXP_LIKE(col, pattern [, flags]) for full regex. ESCAPE clause for literal % or _ in patterns.

SELECT * FROM tbl WHERE name LIKE 'J%'; SELECT * FROM tbl WHERE REGEXP_LIKE(name, '^J', 'i'); -- case-insensitive regex

SQLite

LIKE is case-insensitive for ASCII letters by default (unusual). GLOB provides case-sensitive matching using * and ? instead of % and _.

SELECT * FROM tbl WHERE name LIKE 'j%'; -- matches 'John' AND 'john' SELECT * FROM tbl WHERE name GLOB 'J*'; -- case-sensitive