LIKE

Syntax

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 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 string using . Use 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 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":

Select the days that contain the substring "es":

Select the six-character day names:

With the default collations, LIKE is case-insensitive:

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

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

Optimizing LIKE

• MariaDB can use indexes for LIKE on string columns in the case where the LIKE doesn't start with % or _.

• You can set the variable to 5. If this is done, then the optimizer will read 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 indexes.

• For more complex searches and operations on strings, you can use , which were enhanced in MariaDB 10 (see ).

_{This page is licensed: GPLv2, originally from}