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 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.



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%";

	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)



Optimizing LIKE

MariaDB can use indexes for LIKE on string columns in the case where the
LIKE doesn't start with % or _.
Starting from MariaDB 10.0, one can set the optimiz
er_use_condition_selectivity variable to 5. If this is done, then the
optimizer will read optimizer_selectivity_sampling_limit rows to calculate
the selectivity of the LIKE expression before starting to calculate the
query plan. This can help speed up some LIKE queries by providing the
optimizer with more information about your data.



See Also

For searches on text columns, with results sorted by relevance, see full
-text indexes.
For more complex searches and operations on strings, you can use regular
expressions, which were enhanced in MariaDB 10 (see PCRE Regular Expressions).


 ← LENGTHB
 ↑ String Functions ↑
 LOAD_FILE →



Comments
No comments                                Include Archived

Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views,
information and opinions expressed by this content do not necessarily
represent those of MariaDB or any other party.