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';

The following 'employees' table is used in the examples below.

Retrieves employees whose email starts with 'user_a' (prefix match).

sample_like.sql

SELECT name FROM employees WHERE email LIKE 'user_a%';

+--------+
| name   |
+--------+
| user_a |
+--------+
1 row in set

Retrieves employees whose email ends with 'info' (suffix match).

sample_like.sql

SELECT name FROM employees WHERE email LIKE '%info';

+--------+
| name   |
+--------+
| user_a |
| user_c |
| user_e |
| user_b |
+--------+
4 rows in set

Retrieves employees whose email contains 'user_c' (partial match).

sample_like.sql

SELECT name FROM employees WHERE email LIKE '%user_c%';

+--------+
| name   |
+--------+
| user_e |
+--------+
1 row in set

Retrieves employees whose name is exactly 6 characters long (_ matches one character).

sample_like.sql

SELECT name FROM employees WHERE name LIKE '______';

+--------+
| name   |
+--------+
| user_a |
| user_c |
| user_e |
| user_b |
+--------+
4 rows in set

Excludes employees whose email username starts with 'user_b' (using NOT LIKE to exclude).

sample_like.sql

SELECT name, email FROM employees WHERE email NOT LIKE 'user_b%';

+--------+------------------+
| name   | email            |
+--------+------------------+
| user_a | user_a@wp-p.info |
| user_e | user_c@wp-p.info |
| user_b | user_d@wp-p.info |
+--------+------------------+
3 rows in set

Searches for emails that contain '_' as a literal character (treats _ as a literal).

sample_like.sql

SELECT name FROM employees WHERE email LIKE '%\_%' ESCAPE '\';

+--------+
| name   |
+--------+
| user_a |
| user_c |
| user_e |
| user_b |
+--------+
4 rows in set

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

SELECT name FROM employees WHERE email LIKE 'user_a%';

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 '^user_[ab]';

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

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., 'user_a%') allows the database to use an index efficiently. However, partial matches (e.g., '%user%') and suffix matches (e.g., '%info') 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.