-- Created on savesnippets.com · https://savesnippets.com/wVn5zFwdSNgCRx
-- 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.