MariaDB LIKE Operator

In MariaDB, the LIKE operator matches a given string against a given pattern and returns the matching result.

Note that the MariaDB LIKE operator performs whole string matches, not like the REGEXP operator.

The negation of the LIKE operator is NOT LIKE.

MariaDB LIKE Syntax

The MariaDB LIKE operator is a binary comparison operator that takes two operands. The syntax is as follows:

expression LIKE pattern [ESCAPE 'escape_char']

Here:

  • expression can be a column name, value, or expression (such as function calls).
  • pattern is a text pattern. You can use two wildcards (% and _) in this pattern.
    • % matches zero or more arbitrary characters.
    • _ matches any single character.
  • If you need to match a wildcard, you need to use the escape character \, such as \% and \_.
  • The pattern is not case-sensitive.

If expression matches pattern, the LIKE operator returns 1. Otherwise, it returns 0.

For example:

  • a% matches any string starts with a, such as a, ab, abc.
  • %a matches any string ends with a, such as a, ba, nba.
  • %a% matches any string includes a, such as a, abc, nba, bac.
  • %a%b% matches any string includes a, and a is ahead of b, such as ab, 1a1b1.
  • a_ matches any string starts a and the length of string is 2.
  • _a matches any string end a and the length of string is 2.

MariaDB LIKE Examples

%

% matches zero or more arbitrary characters, the following statement demonstrates the usage of the % wildcard:

SELECT 'abc' LIKE 'a%';

Output:

+-----------------+
| 'abc' LIKE 'a%' |
+-----------------+
|               1 |
+-----------------+

Here, the a% pattern represents a string of any length beginning with a, and thus it matches abc.

Another one:

SELECT 'abc' LIKE '%b%';

Output:

+------------------+
| 'abc' LIKE '%b%' |
+------------------+
|                1 |
+------------------+

Here, the %b% pattern represents a string includes b and thus it matches abc.

_

_ matches a arbitrary character, the following statement demonstrates the use of the _ wildcard:

SELECT 'ab' LIKE 'a_', 'ab' LIKE '_b';

Output:

+----------------+----------------+
| 'ab' LIKE 'a_' | 'ab' LIKE '_b' |
+----------------+----------------+
|              1 |              1 |
+----------------+----------------+

Database example

The following example uses the actor table from the Sakila sample database.

SELECT * FROM actor WHERE first_name LIKE 'P%';

Output:

+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|        1 | PENELOPE   | GUINESS   | 2006-02-15 04:34:33 |
|       46 | PARKER     | GOLDBERG  | 2006-02-15 04:34:33 |
|       54 | PENELOPE   | PINKETT   | 2006-02-15 04:34:33 |
|      104 | PENELOPE   | CRONYN    | 2006-02-15 04:34:33 |
|      120 | PENELOPE   | MONROE    | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+

Performance

Queries containing the LIKE operator are likely to run much slower than other queries, and unless you really need it, you should probably avoid using the operator. It is especially slow to use % as a prefix.

Conclusion

In MariaDB, the LIKE operator matches a given string against a given pattern and returns the matching result.