Skip to main content

Celonis Product Documentation

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:

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:

Query

Column1 

         CASE WHEN ( "Table1"."Haystack" LIKE "Table1"."Needle" ) THEN 'match' ELSE 'no match' END

Input

Output

Table1

Haystack : string

Needle : string

'abcde'

'a'

'abcde'

'bcd'

'abcde'

'abcde'

'abcde'

'e'

'aBcDE'

'e'

Result

Column1 : string

'match'

'match'

'match'

'match'

'match'

[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

Input

Output

Table1

Haystack : string

Needle : string

'abcde'

'a_'

'abcde'

'b_d'

'abcde'

'a%d'

'abcde'

'_e'

Result

Column1 : string

'no match'

'no match'

'no match'

'no match'

[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

Input

Output

Table1

Haystack : string

Needle : string

'abcde'

'a_c_e'

'abcde'

'_b_d_'

'abcde'

'a%d_'

'abcde'

'%e%'

Result

Column1 : string

'match'

'match'

'match'

'match'

[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

Input

Output

Table1

Haystack : string

Needle : string

'abcde'

'a'

'abcde'

'A'

'abcde'

'aBcDe'

'abcde'

'B'

'abcde'

'a_C_e'

'abcde'

'_B%'

'abcde'

'%e%'

'abcde'

'%E%'

'aBcDe'

'AbCdE'

Result

Column1 : string

'match'

'match'

'match'

'match'

'no match'

'no match'

'match'

'no match'

'match'

[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

Input

Output

Table1

Haystack : string

Needle : string

'abcde'

'a'

'abcde'

'A'

'abcde'

'aBcDe'

'abcde'

'B'

'abcde'

'a_C_e'

'abcde'

'_B%'

'abcde'

'%e%'

'abcde'

'%E%'

'aBcDe'

'AbCdE'

Result

Column1 : string

'no match'

'no match'

'no match'

'no match'

'match'

'match'

'no match'

'match'

'no match'

[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

Input

Output

Table1

Needle : string

Haystack : string

null

null

null

' '

null

''

null

' '

null

' '

null

'0'

null

'%'

null

'_'

'abc'

null

Result

Column1 : string

'no match'

'no match'

'no match'

'no match'

'no match'

'no match'

'no match'

'no match'

'no match'

[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

Input

Output

Table1

Haystack : string

Needle : string

'_a'

'\_a'

'%a'

'\%a'

'ab'

'\_b'

'ab'

'\%b'

'a'

'\_'

'aba'

'\%'

Result

Column1 : string

'match'

'match'

'no match'

'no match'

'no match'

'no match'

[8]

There is an escaped wildcard in the <pattern>, this behaves as if no wildcard exists. Escaped wildcards are matched like normal characters and if no other wildcard exists, the comparison is not 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

Output

Table1

Haystack : string

'a'

'a_'

'_a'

'_A'

'b_a'

'_ac'

'b_Ac'

Result

Column1 : string

'Input [a] has no match'

'Input [a_] has no match'

'Input [_a] matches pattern [%\\_a%]'

'Input [_A] matches pattern [\\_a]'

'Input [b_a] matches pattern [%\\_a%]'

'Input [_ac] matches pattern [%\\_a%]'

'Input [b_Ac] matches pattern [\\_a]'

[9]

The expression strings can also be string literals:

Query

Column1 

         CASE WHEN ( "Table1"."Haystack" LIKE 'a' ) THEN 'match' ELSE 'no match' END

Input

Output

Table1

Haystack : string

'a'

'a a'

'aba'

' '

''

null

Result

Column1 : string

'match'

'match'

'match'

'no match'

'no match'

'no match'

[10]

Use LIKE in a FILTER expression:

Query

Filter 

         FILTER "Table1"."Haystack" LIKE "Table1"."Needle";

Column1 

         "Table1"."Haystack"

Column2 

         "Table1"."Needle"

Input

Output

Table1

Haystack : string

Needle : string

'abcde'

'a'

'abcde'

'bcd'

'abcde'

'abcde'

'abcde'

'%e'

'abcde'

'E'

'abcde'

'%E'

'abcde'

'_b'

null

'a'

'abcde'

null

'%a'

'\%a'

'ab'

'\_b'

Result

Column1 : string

Column2 : string

'abcde'

'a'

'abcde'

'bcd'

'abcde'

'abcde'

'abcde'

'%e'

'abcde'

'E'

'%a'

'\%a'