LIKE, wildcards and REGEXP

Searching inside text

= only finds an exact match. Real searches are fuzzier: products whose name contains "desk", customers whose email ends with a certain domain, names that start with a letter. For that, SQL has LIKE.

LIKE compares text against a pattern. Two characters have a special meaning inside the pattern:

Everything else in the pattern must match literally.

Starts with, ends with, contains

SELECT name FROM products WHERE name LIKE 'S%';

+---------------------+
| name                |
+---------------------+
| SQL Basics Handbook |
| Standing Desk Mat   |
+---------------------+

SELECT name FROM products WHERE name LIKE '%Mat';

+-------------------+
| name              |
+-------------------+
| Standing Desk Mat |
| Yoga Mat          |
+-------------------+

SELECT name FROM products WHERE name LIKE '%desk%';

+-------------------+
| name              |
+-------------------+
| Desk Lamp         |
| Standing Desk Mat |
+-------------------+

The first finds names that start with S, the second names that end with Mat, and the third names that contain desk anywhere. Because the default collation ignores case, desk matches Desk. If the column used a case-sensitive (_bin or _cs) collation, case would matter.

Matching a fixed length with _

Each _ stands for one character, so you can match shapes:

SELECT first_name FROM customers WHERE first_name LIKE '____';

+------------+
| first_name |
+------------+
| Asha       |
| Hiro       |
| Jack       |
| Liam       |
+------------+

That finds first names of exactly four letters. A pattern such as 'A___' finds four-letter names starting with A. You can combine wildcards: '_a%' finds values whose second letter is a.

NOT LIKE

Put NOT in front to exclude matches:

SELECT first_name, email FROM customers WHERE email NOT LIKE '%@example.com';

Every sample email ends with @example.com, so this correctly returns nothing. The server prints no table for an empty result set.

Searching for a literal % or _

If the text you want contains % or _, tell MySQL they are ordinary characters by putting a backslash in front:

SELECT 'save 50%' LIKE '%50\%' AS ends_with_percent, 'save 50%' LIKE '%50_' AS wildcard_matches;

+-------------------+------------------+
| ends_with_percent | wildcard_matches |
+-------------------+------------------+
|                 1 |                1 |
+-------------------+------------------+

You can also choose another escape character with ESCAPE, as in LIKE '%50!%' ESCAPE '!'.

Performance: the leading wildcard

An index on a text column is sorted from the first character, like a phone book. LIKE 'Mar%' can jump straight to the Ms and read until the pattern stops matching. But LIKE '%mar%' could match anywhere, so MySQL has to check every row. On a table with a few thousand rows nobody notices. On millions of rows it hurts. For serious text search on large tables, look at a FULLTEXT index instead, which a later lesson on indexes introduces.

Regular expressions with REGEXP

When wildcards are not enough, use REGEXP. A regular expression describes a pattern with its own symbols:

SELECT first_name, last_name FROM customers WHERE last_name REGEXP '^[A-K]';

+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Ben        | Carter    |
| Chloe      | Dubois    |
| Daniel     | Kim       |
| Farid      | Khan      |
+------------+-----------+

SELECT first_name, email FROM customers WHERE email REGEXP '^(asha|ben|chloe)[.]';

+------------+--------------------------+
| first_name | email                    |
+------------+--------------------------+
| Asha       | asha.verma@example.com   |
| Ben        | ben.carter@example.com   |
| Chloe      | chloe.dubois@example.com |
+------------+--------------------------+

The first query finds last names starting with a letter from A to K. The second finds emails beginning with one of three names followed by a dot. Inside square brackets, . is just a dot, which avoids the need to escape it.

MySQL 8 also has functions that behave like the operator: REGEXP_LIKE(text, pattern) tests, REGEXP_REPLACE(text, pattern, replacement) substitutes, and REGEXP_SUBSTR(text, pattern) extracts.

SELECT REGEXP_REPLACE('Call +91-98100-00001 now', '[^0-9]', '') AS digits_only;

+--------------+
| digits_only  |
+--------------+
| 919810000001 |
+--------------+

Regular expressions are powerful but not indexed, and complex patterns are hard to read. Use LIKE when it is enough. The Regex Tester is a handy place to build and check a pattern before putting it into a query.

Common mistakes

• Using = with wildcards: name = 'S%' looks for the literal text S%. Use LIKE.
• Starting every search with % on large tables and wondering why it is slow.
• Forgetting to escape a real % or _.
• Assuming case rules. Matching is case-insensitive or not depending on the column's collation, not on LIKE.
• Writing regular expressions with unescaped special characters, such as . meaning "any character".

Exercise

Write queries to find: (1) products with "Set" in the name, (2) customers whose first name has "a" as the second letter, and (3) customers whose last name starts with a vowel, using REGEXP.

SELECT name FROM products WHERE name LIKE '%Set%';
SELECT first_name FROM customers WHERE first_name LIKE '_a%';
SELECT last_name FROM customers WHERE last_name REGEXP '^[aeiou]';

+---------------------+
| name                |
+---------------------+
| Ceramic Mug Set     |
| Building Blocks Set |
+---------------------+
+------------+
| first_name |
+------------+
| Daniel     |
| Farid      |
| Jack       |
| Kavya      |
+------------+
+-----------+
| last_name |
+-----------+
| Okafor    |
| O'Brien   |
+-----------+