Skip to main content

Celonis Product Documentation

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.

Query

Filter 

         FILTER "caseTable"."location" IN_LIKE ( 'DE%' , 'IT%' , 'FR%' , 'ESP%' , 'CHE%' );

Column1 

         "caseTable"."caseId"

Column2 

         PU_STRING_AGG ( "caseTable" , "activityTable"."activity" , ' - ' )

Input

Output

activityTable

caseId : string

activity : string

timestamp : date

'1'

'Activity A'

Tue Jan 01 2019 13:00:00.000

'1'

'Activity B'

Tue Jan 01 2019 15:00:00.000

'2'

'Activity C'

Fri Jan 04 2019 03:00:00.000

'2'

'Activity D'

Fri Jan 04 2019 19:00:00.000

'3'

'Activity A'

Tue Jan 01 2019 19:00:00.000

'3'

'Activity C'

Tue Jan 01 2019 19:34:00.000

'3'

'Activity B'

Tue Jan 01 2019 19:42:00.000

'4'

'Activity B'

Tue Jan 01 2019 19:00:00.000

'4'

'Activity D'

Wed Jan 02 2019 19:05:00.000

'5'

'Activity D'

Tue Jan 01 2019 19:07:00.000

'6'

'Activity A'

Tue Jan 01 2019 19:11:00.000

'6'

'Activity C'

Tue Jan 01 2019 19:12:00.000

'6'

'Activity D'

Tue Jan 01 2019 19:19:00.000

'7'

'Activity A'

Tue Jan 01 2019 10:00:00.000

'7'

'Activity B'

Tue Jan 01 2019 12:00:00.000

'7'

'Activity C'

Tue Jan 01 2019 17:00:00.000

'7'

'Activity D'

Tue Jan 01 2019 18:30:00.000

'7'

'Activity E'

Tue Jan 01 2019 19:00:00.000

'8'

'Activity A'

Tue Jan 01 2019 20:01:00.000

'8'

'Activity B'

Tue Jan 01 2019 20:05:00.000

'8'

'Activity E'

Tue Jan 01 2019 20:22:00.000

'9'

'Activity A'

Tue Jan 01 2019 14:01:00.000

'9'

'Activity C'

Tue Jan 01 2019 14:07:00.000

caseTable

caseId : string

location : string

'1'

'DE_MUNICH'

'2'

'US_NEWYORK'

'3'

'FR_PARIS'

'4'

'DE_BERLIN'

'5'

'UK_LONDON'

'6'

'UK_LONDON'

'7'

'IT_MILAN'

'8'

'ESP_MADRID'

'9'

'CHE_ZURICH'

Foreign Keys

activityTable.caseId

caseTable.caseId

Result

Column1 : string

Column2 : string

'1'

'Activity A - Activity B'

'3'

'Activity A - Activity C - Activity B'

'4'

'Activity B - Activity D'

'7'

'Activity A - Activity B - Activity C - Activity D - Activity E'

'8'

'Activity A - Activity B - Activity E'

'9'

'Activity A - Activity C'

[2]

This example shows the usage of IN_LIKE with NULL values.

Query

Filter 

         FILTER "Table1"."Column" IN_LIKE ( '%A%' , '%B%' );

Column1 

         "Table1"."Column"

Input

Output

Table1

Column : string

'asddqqW_A_W'

null

'fdsn_B'

null

'fdskjd_B_dsa'

'dsaksdj'

null

Result

Column1 : string

'asddqqW_A_W'

'fdsn_B'

'fdskjd_B_dsa'

[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.

Query

Filter 

         FILTER "Table1"."Column" NOT IN_LIKE ( 'test' , 'xyz' , 'celonis' , '%PQL%' , '_' );

Column1 

         "Table1"."Column"

Input

Output

Table1

Column : string

'hallo IN_LIKE'

'Axyz'

'vamos a la playa'

'test test 1,2,3'

'celosphere'

'celosphere and celonis'

Result

Column1 : string

'hallo IN_LIKE'

'vamos a la playa'

'celosphere'

[4]

This example shows the usage of IN_LIKE with a column containing the pattern.

Query

Filter 

         FILTER "Table1"."Column" IN_LIKE ( "Table2"."SEARCH" );

Column1 

         "Table1"."Column"

Input

Output

Table1

Column : string

'hallo IN_LIKE test'

'Axyz'

'vamos a la playa'

'test test 1,2,3'

'celosphere'

'celosphere and celonis'

'123'

Table2

SEARCH : string

'test'

'xyz'

'celonis'

'___'

Table3

SEARCH : string

'words'

'celosphere'

'ha_lo'

'%1_2_3'

'baz'

'%IN_LIKE%'

'123'

'12931'

'he%y'

'r_g'

'asdf'

Result

Column1 : string

'hallo IN_LIKE test'

'Axyz'

'test test 1,2,3'

'celosphere and celonis'

'123'

[5]

This example shows the usage of NOT IN_LIKE with a column containing the pattern.

Query

Filter 

         FILTER "Table1"."Column" NOT IN_LIKE ( "Table2"."SEARCH" );

Column1 

         "Table1"."Column"

Input

Output

Table1

Column : string

'hallo IN_LIKE test'

'Axyz'

'vamos a la playa'

'test test 1,2,3'

'celosphere'

'celosphere and celonis'

'123'

Table2

SEARCH : string

'test'

'xyz'

'celonis'

'___'

Table3

SEARCH : string

'words'

'celosphere'

'ha_lo'

'%1_2_3'

'baz'

'%IN_LIKE%'

'123'

'12931'

'he%y'

'r_g'

'asdf'

Result

Column1 : string

'vamos a la playa'

'celosphere'

[6]

Check if a constant value matches one of the search patterns:

Query

Column1 

         CASE WHEN 'celonis' IN_LIKE ( "Table2"."SEARCH" ) THEN 1 ELSE 0 END

Input

Output

Table1

Column : string

'hallo IN_LIKE test'

'Axyz'

'vamos a la playa'

'test test 1,2,3'

'celosphere'

'celosphere and celonis'

'123'

Table2

SEARCH : string

'test'

'xyz'

'celonis'

'___'

Table3

SEARCH : string

'words'

'celosphere'

'ha_lo'

'%1_2_3'

'baz'

'%IN_LIKE%'

'123'

'12931'

'he%y'

'r_g'

'asdf'

Result

Column1 : int

1

See also: