LIKE
Description
The LIKE
predicate determines whether the right expression string matches the left expression string. The output can be negated by using NOT LIKE
.
LIKE can only be applied to STRING values.
[NOT] LIKE
can be used in the following contexts:
CASE WHEN (in the
WHEN
conditions)Pull-Up-Functions (in the filter argument)
BIND_FILTERS (in the filter argument)
CALC_REWORK (in the filter argument)
Consider using IN_LIKE if you want to match the left expression against multiple patterns.
Syntax
table.column LIKE pattern
table.column NOT LIKE pattern
NULL handling
If any input expression is NULL, NULL is returned.
Performance
It is discouraged to use the LIKE operator for equality and inequality matching due to performance reasons. Instead, it is recommended to use the '=' operator for equality and the '!=' operator for inequality.
Wildcards
The following wildcards can be used in the right pattern expression:
%: Matches any string with zero or more characters.
_: Matches exactly one character.
Implicit Wildcards
If no wildcard character is present in the <pattern>
, leading and trailing %
wildcards are implicitly assumed: %<pattern>%
. When wildcards are implicitly assumed, then comparison is not case sensitive.
Wildcard Escaping
Wildcards can be escaped with backslash for matching the literal wildcard characters in a STRING (\%
, \_
). For more details on escape sequences see the STRING data type.
Examples
[1] No wildcards are included. LIKE will assume leading and trailing '%' wildcards, which means that LIKE matches in all examples: | ||||||||||||||||||||||
| ||||||||||||||||||||||
|
[2] Wildcards are included. LIKE will not leading and trailing '%' wildcards, which means that LIKE does not match in all examples: | |||||||||||||||||||
| |||||||||||||||||||
|
[3] Wildcards are included. LIKE will not leading and trailing '%' wildcards, but in all examples, LIKE matches: | |||||||||||||||||||
| |||||||||||||||||||
|
[4] If no wildcards are included, comparison is case insensitive. If wildcards are included, comparison is case sensitive: | ||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||
|
[5] | ||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||
|
[6] If any side of the LIKE operator is NULL, NULL is returned: | ||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||
|
[7] Wildcards can be escaped with a backslash. In a STRING column no escaping of the backslash escape character itself is needed: | |||||||||||||||||||||||||
| |||||||||||||||||||||||||
|
[8] There is an escaped wildcard in the | ||||||||||||||||||||
| ||||||||||||||||||||
|
[9] The expression strings can also be string literals: | ||||||||||||||||||
| ||||||||||||||||||
|
[10] Use LIKE in a FILTER expression: | ||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||
|