`LIKE` uses `%` (any string) and `_` (single char). PostgreSQL `ILIKE` is case-insensitive. For real regex, reach for `SIMILAR TO` (POSIX) or `~` (PostgreSQL).
SQL
-- Starts with, ends with, contains
SELECT * FROM users WHERE email LIKE 'admin%';
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM users WHERE email LIKE '%@%.gov%';
-- Case-insensitive (PostgreSQL only — use LOWER() elsewhere)
SELECT * FROM users WHERE email ILIKE '%@GMAIL.COM';
-- Cross-DB case-insensitive
SELECT * FROM users WHERE LOWER(email) LIKE '%@gmail.com';
-- Escape literal % or _ in user input
SELECT * FROM products WHERE name LIKE '50\% off%' ESCAPE '\';
-- PostgreSQL regex match
SELECT * FROM users WHERE email ~ '^[a-z]+\d+@';
-- Performance: leading % prevents index use.
-- Use trigram (pg_trgm) or full-text search for substring search at scale.Save your own code snippets
Create a free account and build your private vault. Share publicly whenever you want.