Applies to: CELONIS 4.0 CELONIS 4.2 CELONIS 4.3 CELONIS 4.4 CELONIS 4.5 CELONIS 4.6 CELONIS 4.7
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) - FILTER
- Pull-Up-Functions (in the filter argument)
- BIND_FILTERS (in the filter argument)
- CALC_REWORK (in the filter argument)
Syntax
table.column LIKE pattern
table.column NOT LIKE pattern
NULL handling
If any input expression is NULL, NULL is returned.
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:
Query
Column1
CASE WHEN ( "Table1"."Haystack" LIKE "Table1"."Needle" ) THEN 'match' ELSE 'no match' END
[2] Wildcards are included. LIKE will not leading and trailing '%' wildcards, which means that LIKE does not match in all examples:
Query
Column1
CASE WHEN ( "Table1"."Haystack" LIKE "Table1"."Needle" ) THEN 'match' ELSE 'no match' END
[3] Wildcards are included. LIKE will not leading and trailing '%' wildcards, but in all examples, LIKE matches:
Query
Column1
CASE WHEN ( "Table1"."Haystack" LIKE "Table1"."Needle" ) THEN 'match' ELSE 'no match' END
[4] If no wildcards are included, comparison is case insensitive. If wildcards are included, comparison is case sensitive:
Query
Column1
CASE WHEN ( "Table1"."Haystack" LIKE "Table1"."Needle" ) THEN 'match' ELSE 'no match' END
[5]
NOT LIKE
: Results are negated. If no wildcards are included, comparison is case insensitive.
If wildcards are included, comparison is case sensitive:
Query
Column1
CASE WHEN ( "Table1"."Haystack" NOT LIKE "Table1"."Needle" ) THEN 'match' ELSE 'no match' END
[6] If any side of the LIKE operator is NULL, NULL is returned:
Query
Column1
CASE WHEN ( "Table1"."Haystack" LIKE "Table1"."Needle" ) THEN 'match' ELSE 'no match' END
[7] Wildcards can be escaped with a backslash. In a STRING column no escaping of the backslash escape character itself is needed:
Query
Column1
CASE WHEN ( "Table1"."Haystack" LIKE "Table1"."Needle" ) THEN 'match' ELSE 'no match' END
[8] There is an escaped wildcard in the
<pattern>
, so no implicit wildcards are added and
comparison is case sensitive. Note that in STRING constants the backslash escape character itself must be escaped with another backslash:
Query
Column1
CASE WHEN "Table1"."Haystack" LIKE '\\_a' THEN 'Input [' || "Table1"."Haystack" || '] matches pattern [\\\\_a]' WHEN "Table1"."Haystack" LIKE '%\\_a%' THEN 'Input [' || "Table1"."Haystack" || '] matches pattern [%\\\\_a%]' ELSE 'Input [' || "Table1"."Haystack" || '] has no match' END
Input
Table1
Haystack : STRING |
---|
'a' |
'a_' |
'_a' |
'_A' |
'b_a' |
'_ac' |
'b_Ac' |
Output
Result
Column1 : STRING |
---|
'Input [a] has no match' |
'Input [a_] has no match' |
'Input [_a] matches pattern [\\_a]' |
'Input [_A] has no match' |
'Input [b_a] matches pattern [%\\_a%]' |
'Input [_ac] matches pattern [%\\_a%]' |
'Input [b_Ac] has no match' |
[10] Use LIKE in a FILTER expression:
Query
Filter
FILTER "Table1"."Haystack" LIKE "Table1"."Needle";
Column1
"Table1"."Haystack"
Column2
"Table1"."Needle"