IN_LIKE
Description
The IN_LIKE
predicate determines whether one of the multiple right string patterns matches the left expression string. The left expression could either be a column or a constant string. The pattern list has to contain at least one entry while all entries have to be STRING values. The output can be negated by using NOT IN_LIKE
.
IN_LIKE can only be applied to STRING values and STRING columns.
Warning
Computation Times In some settings, this operator may require excessive CPU time. If the execution time exceeds 10 minutes, the execution is stopped and an according error is reported.
[NOT] IN_LIKE
can be used in the following contexts:
Syntax
value/column IN_LIKE ( value1/column1, ... )
value/column NOT IN_LIKE ( value1/column1, ... )
NULL handling
A value in the pattern list on the right can also be NULL. A NULL value matches with NULL values in the pattern list. NULL values originating from columns on the right are ignored.
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.
Tips
If there is a statement like <table.column> LIKE <pattern1> OR ... OR <table.column> LIKE <patternN>
, it can be shortened by using IN_LIKE.
Examples
[1] This query shows a sample use case for IN_LIKE. Here, only countries within the EU should be considered. All other countries should be ignored. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
[2] This example shows the usage of IN_LIKE with NULL values. | ||||||||||||||||
| ||||||||||||||||
|
[3] This query shows the usage of NOT IN_LIKE. It is also important to mention that the use of wildcards within the patterns can vary. | |||||||||||||||
| |||||||||||||||
|
[4] This example shows the usage of IN_LIKE with a column containing the pattern. | |||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||
|
[5] This example shows the usage of NOT IN_LIKE with a column containing the pattern. | ||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||
|
[6] Check if a constant value matches one of the search patterns: | |||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||
|