LIKE

Syntax:

expr LIKE pat [ESCAPE 'escape_char']
expr NOT LIKE pat [ESCAPE 'escape_char']

Description:

Tests whether expr matches the pattern pat. Returns either 1 (TRUE) or 0 (FALSE). Both expr and pat may be any valid expression and are evaluated to strings. Patterns may use the following wildcard characters:

• % matches any number of characters, including zero.
• _ matches any single character.

Use NOT LIKE to test if a string does not match a pattern. This is equivalent to using the NOT operator on the entire LIKE expression.

If either the expression or the pattern is NULL, the result is NULL.

LIKE performs case-insensitive substring matches if the collation for the expression and pattern is case-insensitive. For case-sensitive matches, declare either argument to use a binary collation using COLLATE, or coerce either of them to a BINARY string using CAST. Use SHOW COLLATION to get a list of available collations. Collations ending in _bin are case-sensitive.

Numeric arguments are coerced to binary strings.

The _ wildcard matches a single character, not byte. It will only match a multi-byte character if it is valid in the expression's character set. For example, _ will match _utf8"€", but it will not match _latin1"€" because the Euro sign is not a valid latin1 character. If necessary, use CONVERT to use the expression in a different character set.

If you need to match the characters _ or %, you must escape them. By default, you can prefix the wildcard characters the backslash characer \ to escape them. The backslash is used both to encode special characters like newlines when a string is parsed as well as to escape wildcards in a pattern after parsing. Thus, to match an actual backslash, you sometimes need to double-escape it as "\\\\".

To avoid difficulties with the backslash character, you can change the wildcard escape character using ESCAPE in a LIKE expression. The argument to ESCAPE must be a single-character string.

Examples:

Select the days that begin with "T":

CREATE TABLE t1 (d VARCHAR(16));
INSERT INTO t1 VALUES ("Monday"), ("Tuesday"), ("Wednesday"), ("Thursday"), ("Friday"), ("Saturday"), ("Sunday");
SELECT * FROM t1 WHERE d LIKE "T%";

MariaDB [test]> SELECT * FROM t1 WHERE d LIKE "T%";
+----------+
| d        |
+----------+
| Tuesday  |
| Thursday |
+----------+
2 rows in set (0.00 sec)

Select the days that contain the substring "es":

SELECT * FROM t1 WHERE d LIKE "%es%";

MariaDB [test]> SELECT * FROM t1 WHERE d LIKE "%es%";
+-----------+
| d         |
+-----------+
| Tuesday   |
| Wednesday |
+-----------+
2 rows in set (0.00 sec)

Select the six-character day names:

SELECT * FROM t1 WHERE d like "___day";

MariaDB [test]> SELECT * FROM t1 WHERE d like "___day";
+---------+
| d       |
+---------+
| Monday  |
| Friday  |
| Sunday  |
+---------+
3 rows in set (0.00 sec)

With the default collations, LIKE is case-insensitive:

SELECT * FROM t1 where d like "t%";

MariaDB [test]> SELECT * FROM t1 where d like "t%";
+----------+
| d        |
+----------+
| Tuesday  |
| Thursday |
+----------+
2 rows in set (0.00 sec)

Use COLLATE to specify a binary collation, forcing case-sensitive matches:

SELECT * FROM t1 WHERE d like "t%" COLLATE latin1_bin;

MariaDB [test]> SELECT * FROM t1 WHERE d like "t%" COLLATE latin1_bin;
Empty set (0.00 sec)

You can include functions and operators in the expression to match. Select dates based on their day name:

CREATE TABLE t2 (d DATETIME);
INSERT INTO t2 VALUES
    ("2007-01-30 21:31:07"),
    ("1983-10-15 06:42:51"),
    ("2011-04-21 12:34:56"),
    ("2011-10-30 06:31:41"),
    ("2011-01-30 14:03:25"),
    ("2004-10-07 11:19:34");
SELECT * FROM t2 WHERE DAYNAME(d) LIKE "T%";

MariaDB [test]> SELECT * FROM t2 WHERE DAYNAME(d) LIKE "T%";
+------------------+
| d                |
+------------------+
| 2007-01-30 21:31 |
| 2011-04-21 12:34 |
| 2004-10-07 11:19 |
+------------------+
3 rows in set, 7 warnings (0.00 sec)