Skip to content
Tutorials Center
My learning

Topics

Lesson 8 of 40 ยท SQL Basics

Pattern Matching with LIKE and REGEXP in MariaDB

Match text in MariaDB with LIKE wildcards, ESCAPE, NOT LIKE and case rules, then use REGEXP and RLIKE (PCRE) for advanced patterns, REGEXP_REPLACE and REGEXP_SUBSTR, with runnable stand-ins.

Exact comparisons with = are not enough when you search for names that "start with Mar", emails from one domain or phone numbers in a certain format. MariaDB gives you two tools: LIKE for simple wildcard patterns, and REGEXP for full regular expressions. This lesson covers both, plus the collation rules that decide whether matches are case sensitive.

LIKE and its two wildcards

LIKE compares a string to a pattern with two special characters:

Wildcard Matches
% any number of characters, including none
_ exactly one character
SQL
SELECT * FROM customers WHERE last_name LIKE 'Sm%';     -- starts with Sm
SELECT * FROM customers WHERE email LIKE '%@example.com'; -- ends with
SELECT * FROM products  WHERE name LIKE '%board%';       -- contains
SELECT * FROM products  WHERE name LIKE '_ook%';         -- one char then "ook"
SQL Starts with, ends with, contains ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

NOT LIKE negates the test. A pattern with no wildcard behaves like =. Remember that LIKE 'abc%' can use an index on that column (the prefix is known), while LIKE '%abc' and LIKE '%abc%' need to scan every row. For real text search on large tables, consider a FULLTEXT index.

Case sensitivity

In MariaDB the result depends on the collation of the column. Collations ending in _ci ignore case, so 'smith' LIKE 'SMITH' is true; _cs and _bin collations are case sensitive. You can force the comparison:

SQL
SELECT * FROM customers WHERE last_name LIKE 'smith' COLLATE utf8mb4_bin;  -- case sensitive
SELECT * FROM customers WHERE LOWER(last_name) LIKE 'smith%';              -- portable but skips indexes

SQLite (the runner here) is case-insensitive for plain ASCII with LIKE, which behaves like a _ci collation:

SQL Case-insensitive match ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Searching for % and _ themselves

To match a literal wildcard character, put an escape character in front of it. The default is the backslash, and you can pick another with ESCAPE:

SQL
SELECT * FROM coupons WHERE code LIKE '10\%OFF';        -- literal %
SELECT * FROM coupons WHERE code LIKE '10!%OFF' ESCAPE '!';
SQL ESCAPE a wildcard

Press Run (or Ctrl + Enter) to see the result here.

REGEXP and RLIKE

For anything LIKE can't express, use regular expressions. REGEXP and RLIKE are synonyms; since MariaDB 10.0.5 they use the PCRE library, so Perl-style patterns work (lookaheads, \d, \b, non-greedy *?):

SQL
SELECT * FROM customers WHERE last_name REGEXP '^(Smith|Smyth)$';
SELECT * FROM products  WHERE name REGEXP '[0-9]{3}';           -- three digits in a row
SELECT * FROM customers WHERE email REGEXP '^[a-z0-9._-]+@[a-z0-9.-]+\\.[a-z]{2,}$';
SELECT * FROM products  WHERE name NOT REGEXP '^[A-Z]';

The building blocks:

Pattern Meaning
^ and $ start and end of the string
. any single character
[abc], [a-z], [^0-9] a set, a range, anything except
*, +, ? zero or more, one or more, optional
, {n,m} repeat n times, or n to m times
`(a b)`
\\d, \\w, \\s digit, word character, space (backslash doubled in a SQL string)

Because the pattern is a string, every backslash must be doubled: '\\d+' in SQL is \d+ for the regex engine. Unlike LIKE, a regex matches anywhere inside the text unless you anchor it with ^ and $. REGEXP cannot use an ordinary index, so reserve it for filtering rows you have already narrowed down.

MariaDB also offers functions based on the same engine:

SQL
SELECT REGEXP_REPLACE('Call 555-1234 or 555-9876', '[0-9]{3}-[0-9]{4}', '###-####');
SELECT REGEXP_SUBSTR('order #4821 shipped', '[0-9]+');          -- 4821
SELECT REGEXP_INSTR('order #4821 shipped', '[0-9]+');           -- position 8

REGEXP_REPLACE is the tool for bulk-cleaning data, for instance stripping everything except digits from phone numbers: UPDATE contacts SET phone = REGEXP_REPLACE(phone, '[^0-9]', '');.

A runnable stand-in

The in-browser engine has no REGEXP function, but it has GLOB (Unix-style patterns with *, ? and [a-z], case sensitive), which is enough to practise the thinking: describe a shape and filter by it. Compare with the MariaDB line in each comment:

SQL Shape matching with GLOB ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

When you need regex cleaning in SQLite-like environments, do it in two steps: find candidates with GLOB/LIKE, then transform with REPLACE, SUBSTR and INSTR:

SQL Clean a value with REPLACE

Press Run (or Ctrl + Enter) to see the result here.

LIKE or REGEXP?

Need Use
Prefix search on an indexed column LIKE 'abc%'
Simple contains search LIKE '%abc%' (slow on big tables)
Alternatives, character classes, repetition REGEXP
Natural-language search on large text FULLTEXT index with MATCH ... AGAINST
Validation Do it in the application, and use CHECK constraints for hard rules

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does the pattern 'a_c' match with LIKE?

    The underscore stands for exactly one character, while % stands for any number of characters.
  2. 2. Which LIKE pattern can use an index on the column?

    When the pattern starts with fixed characters the engine can search the index range. A leading wildcard forces a scan.
  3. 3. How do you write the regex \d+ inside a MariaDB string literal?

    The backslash is an escape character in SQL strings, so it must be doubled to reach the regex engine.
  4. 4. What does REGEXP '^[A-C]' find?

    The caret anchors the match at the start; the bracket set matches one of the three letters.

Summary

  • LIKE uses % (any text) and _ (one character); escape literal wildcards with ESCAPE or a backslash.
  • Case sensitivity follows the column collation: _ci is insensitive, _bin is sensitive.
  • REGEXP/RLIKE use PCRE; double the backslashes and anchor with ^ and $ when you need a full match.
  • REGEXP_REPLACE, REGEXP_SUBSTR and REGEXP_INSTR transform and extract text.

Next: NULL, variables and quoting