SQL · Chapter 11 of 42
SQL LIKE
LIKE performs pattern matching on text.
Wildcards: `%` matches ANY sequence of characters, `_` matches EXACTLY ONE character.
Syntax
SELECT ... FROM table WHERE col LIKE 'pattern';Case sensitivity
Depends on the database and collation. PostgreSQL is case-sensitive by default; MySQL usually not. Use ILIKE (Postgres) for guaranteed case-insensitive match.
Performance
`LIKE 'abc%'` can use an index. `LIKE '%abc'` (leading wildcard) usually cannot — it forces a scan.
Example 1 (sql)
SELECT name FROM users WHERE name LIKE 'A%';Output
Ana
Amit
ArunNames starting with 'A'.
Example 2 (sql)
SELECT * FROM users WHERE email LIKE '%@gmail.com';Output
All Gmail usersAnything ending with '@gmail.com'.
Key points
- `%` = any sequence, `_` = single char.
- PostgreSQL uses ILIKE for case-insensitive.
- Leading `%` disables index use.
- Escape literal `%` and `_` with a backslash or ESCAPE clause.
💡 Note: For full-text search (blogs, product search) LIKE isn't enough — use a search index like PostgreSQL's tsvector or Elasticsearch.