LIKE

-- Retrieves rows that match the pattern.
SELECT column FROM table WHERE column LIKE 'pattern';

-- Retrieves rows that do not match the pattern.
SELECT column FROM table WHERE column NOT LIKE 'pattern';

-- Uses ESCAPE to treat a wildcard character as a literal.
SELECT column FROM table WHERE column LIKE 'pattern' ESCAPE 'escape_char';

-- Retrieves employees whose name starts with "Ta" (prefix match).
SELECT name FROM employees WHERE name LIKE 'Ta%';

-- Retrieves employees whose name ends with "ko" (suffix match).
SELECT name FROM employees WHERE name LIKE '%ko';

-- Retrieves employees whose name contains "na" (partial match).
SELECT name FROM employees WHERE name LIKE '%na%';

-- Retrieves employees whose name is exactly 3 characters long (_ matches one character).
SELECT name FROM employees WHERE name LIKE '___';

-- Retrieves employees whose email is not from the "example" domain.
SELECT name, email FROM employees WHERE email NOT LIKE '%@example.com';

-- Searches for products whose name contains "%OFF" (treats % as a literal character).
SELECT product_name FROM products WHERE product_name LIKE '%\%OFF' ESCAPE '\';

-- Result of: SELECT name FROM employees WHERE name LIKE 'Ta%';
-- +----------+
-- | name     |
-- +----------+
-- | Tanaka   |
-- | Tamura   |
-- +----------+

The basic syntax of LIKE with the % and _ wildcards works the same across all major databases.

SELECT name FROM employees WHERE name LIKE 'Ta%';

Case sensitivity varies by database. MySQL is case-insensitive by default (when the collation is ci). PostgreSQL is case-sensitive, so use ILIKE for case-insensitive searches.

-- PostgreSQL (case-insensitive LIKE)
SELECT name, email FROM employees WHERE email ILIKE '%@EXAMPLE.COM';

For advanced pattern matching with regular expressions, each database uses its own syntax.

-- MySQL (REGEXP operator)
SELECT name FROM employees WHERE name REGEXP '^[TN]';

-- PostgreSQL (~ operator)
SELECT name FROM employees WHERE name ~ '^[TN]';

-- Oracle (REGEXP_LIKE function)
SELECT name FROM employees WHERE REGEXP_LIKE(name, '^[TN]');

LIKE is a widely used operator for text searching. % matches any sequence of characters, and _ matches exactly one character.

Using LIKE with a prefix pattern (e.g., 'Ta%') allows the database to use an index efficiently. However, partial matches (e.g., '%ta%') and suffix matches (e.g., '%ta') cannot use an index and result in a full table scan. Use caution when querying large tables.

In MySQL, the default case-insensitive collation (ci: case insensitive) means LIKE 'abc%' and LIKE 'ABC%' return the same results. To perform a case-sensitive search, use the BINARY keyword.