expr LIKE pat [ESCAPE 'escape_char'] expr NOT LIKE pat [ESCAPE 'escape_char']
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 character \
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.
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%";
SELECT * FROM t1 WHERE d LIKE "T%"; +----------+ | d | +----------+ | Tuesday | | Thursday | +----------+
Select the days that contain the substring "es":
SELECT * FROM t1 WHERE d LIKE "%es%";
SELECT * FROM t1 WHERE d LIKE "%es%"; +-----------+ | d | +-----------+ | Tuesday | | Wednesday | +-----------+
Select the six-character day names:
SELECT * FROM t1 WHERE d like "___day";
SELECT * FROM t1 WHERE d like "___day"; +---------+ | d | +---------+ | Monday | | Friday | | Sunday | +---------+
With the default collations, LIKE
is case-insensitive:
SELECT * FROM t1 where d like "t%";
SELECT * FROM t1 where d like "t%"; +----------+ | d | +----------+ | Tuesday | | Thursday | +----------+
Use COLLATE
to specify a binary collation, forcing case-sensitive matches:
SELECT * FROM t1 WHERE d like "t%" COLLATE latin1_bin;
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%";
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)
%
or _
.
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/like/