Applies to:  CELONIS 4.0 CELONIS 4.2 CELONIS 4.3 CELONIS 4.4 


Description

The LIKE predicate determines whether the right expression string matches the left expression string.

LIKE can only be applied to STRING values. If any input expression is NULL, NULL is returned.

Usually, it is used inside CASE WHEN or FILTER conditions.

Wildcards

The following wildcards can be used in the right pattern expression:

  • %: Matches any string with zero or more characters.
  • _: Matches exactly one character.

Wildcards can be escaped with a backslash (\%, \_). If no wildcards are contained, leading and trailing % wildcards are assumed, and comparison is not case sensitive.

Syntax

table.column LIKE pattern

Examples


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
Input
Table1
Haystack : STRINGNeedle : STRING
'abcde''a'
'abcde''bcd'
'abcde''abcde'
'abcde''e'
'aBcDE''e'
Output
Result
Column1 : STRING
'match'
'match'
'match'
'match'
'match'



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
Input
Table1
Haystack : STRINGNeedle : STRING
'abcde''a_'
'abcde''b_d'
'abcde''a%d'
'abcde''_e'
Output
Result
Column1 : STRING
'no match'
'no match'
'no match'
'no match'



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
Input
Table1
Haystack : STRINGNeedle : STRING
'abcde''a_c_e'
'abcde''_b_d_'
'abcde''a%d_'
'abcde''%e%'
Output
Result
Column1 : STRING
'match'
'match'
'match'
'match'



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
Input
Table1
Haystack : STRINGNeedle : STRING
'abcde''a'
'abcde''A'
'abcde''aBcDe'
'abcde''B'
'abcde''a_C_e'
'abcde''_B%'
'abcde''%e%'
'abcde''%E%'
'aBcDe''AbCdE'
Output
Result
Column1 : STRING
'match'
'match'
'match'
'match'
'no match'
'no match'
'match'
'no match'
'match'



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
Input
Table1
Needle : STRINGHaystack : STRING
nullnull
null' '
null''
null' '
null' '
null'0'
null'%'
null'_'
'abc'null
Output
Result
Column1 : STRING
null
null
null
null
null
null
null
null
null



Wildcards can be escaped with a backslash:


Query
Column1
CASE WHEN ( "Table1"."Haystack" LIKE "Table1"."Needle" ) THEN 'match' ELSE 'no match' END
Input
Table1
Haystack : STRINGNeedle : STRING
'_a''\_a'
'%a''\%a'
'ab''\_b'
'ab''\%b'
'a''\_'
'aba''\%'
Output
Result
Column1 : STRING
'match'
'match'
'no match'
'no match'
'no match'
'no match'



The expression strings can also be string literals:


Query
Column1
CASE WHEN ( "Table1"."Haystack" LIKE 'a' ) THEN 'match' ELSE 'no match' END
Input
Table1
Haystack : STRING
'a'
'a a'
'aba'
' '
''
null
Output
Result
Column1 : STRING
'match'
'match'
'match'
'no match'
'no match'
null



Use LIKE in a FILTER expression:
Query
Filter
FILTER "Table1"."Haystack" LIKE "Table1"."Needle"
Column1
"Table1"."Haystack"
Column2
"Table1"."Needle"
Input
Table1
Haystack : STRINGNeedle : STRING
'abcde''a'
'abcde''bcd'
'abcde''abcde'
'abcde''%e'
'abcde''E'
'abcde''%E'
'abcde''_b'
null'a'
'abcde'null
'%a''\%a'
'ab''\_b'
Output
Result
Column1 : STRINGColumn2 : STRING
'abcde''a'
'abcde''bcd'
'abcde''abcde'
'abcde''%e'
'abcde''E'
'%a''\%a'



  • No labels