Skip to main content

Celonis Product Documentation

STRING_SPLIT
Description

STRING_SPLIT splits the string into sub-strings based on pattern and returns the split-index-th split.

STRING_SPLIT uses zero-based indexing, i.e., the first split is at split-index=0. If the requested split does not exist in the string, NULL is returned.

Supported input column types: STRING

Output column type: STRING

Syntax
 STRING_SPLIT ( table.column, pattern, split-index )

  • pattern is the sequence of characters to split the string on. This can be multi-character but does not support wildcards.

  • split-index is the zero-based index of the requested sub-string after splitting the string. A negative index value selects splits from the end of the string. For example, -1 refers to the last split in the string, -2 the second last and so on.

The following apply to special values of pattern, split-index and the input-string(from table.column):

  • split-index is negative: splits are selected from the end of the input-string with -1 being the last split.

  • pattern is empty: split-index will behave as if indexing into individual characters of the input-string.

  • pattern does not exist in the string and split-index is zero: the entire string is returned.

  • pattern appears at the beginning or end of the input-string: the split contains an empty string at the beginning or end respectively.

  • pattern is repeated consecutively in the input-string: the split behaves as if there are empty strings in between the repeated consecutive patterns.

  • pattern is identical to input-string and split-index is either zero or one: An empty string is returned.

NULL handling

If any of the inputs are NULL, the result is NULL as well.

Examples

[1]

Return the first split after splitting on ','.

Query

Column1 

         STRING_SPLIT ( "Table1"."Column1" , ',' , 0 )

Input

Output

Table1

Column1 : string

'äö,ü,abc'

Result

Column1 : string

'äö'

[2]

Return the first split after splitting on the multi-character pattern ', '.

Query

Column1 

         STRING_SPLIT ( "Table1"."Column1" , ', ' , 0 )

Input

Output

Table1

Column1 : string

'FirstName, MiddleName, LastName'

'Date, Notes'

''

null

', '

', abcd, '

', , '

Result

Column1 : string

'FirstName'

'Date'

''

null

''

''

''

[3]

Return the second split after splitting on the multi-character pattern ', '.

Query

Column1 

         STRING_SPLIT ( "Table1"."Column1" , ', ' , 1 )

Input

Output

Table1

Column1 : string

'FirstName, MiddleName, LastName'

'Date, Notes'

''

null

', '

', abcd, '

', , '

Result

Column1 : string

'MiddleName'

'Notes'

null

null

''

'abcd'

''

[4]

Extract the second character from the input using an empty pattern string.

Query

Column1 

         STRING_SPLIT ( "Table1"."Column1" , '' , 1 )

Input

Output

Table1

Column1 : string

'FirstName, LastName'

'äö,ü,'

'abcd'

''

null

Result

Column1 : string

'i'

'ö'

'b'

''

null

[5]

Return the last split using a negative index (-1).

Query

Column1 

         STRING_SPLIT ( "Table1"."Column1" , ', ' , - 1 )

Input

Output

Table1

Column1 : string

'FirstName, LastName'

'Query'

null

Result

Column1 : string

'LastName'

'Query'

null

[6]

Applies a FILTER so that only rows with CaseID not equal to 2 are taken into account.

Query

Filter 

         FILTER "Table1"."CaseID" != 2;

Column1 

         "Table1"."CaseID"

Column2 

         STRING_SPLIT ( "Table1"."Customer" , '-' , 1 )

Input

Output

Table1

CaseID : int

Customer : string

1

'Customer-X'

2

'Customer-Y'

3

'Customer-Z'

Result

Column1 : int

Column2 : string

1

'X'

3

'Z'