Applies to:  CELONIS 4.7 

Description

PATINDEX searches for the pattern-substring and returns its index (starting from 1) within the input if it was found (zero otherwise).

Supported input column types: STRING

Output column type: INT

Syntax

PATINDEX ( table.column, pattern [, occurrence ] )
  • table.column: The string column on which the PATINDEX operator is executed.
  • pattern: The pattern which is searched for in the input string.
  • occurrence: Number of the occurrence of the pattern, defaults to 1. Only the index of the n-th occurrence of the pattern is returned, if the n-th occurrence exists, otherwise 0 is returned.

NOTE: In general, this operator conforms to the behaviour of MS SQL Server but the parameter order is changed. For consistency with other operators (such as LIKE), the first parameter of PATINDEX is the input string and the second parameter is the pattern string.

NULL handling

If any of the inputs (table.column or pattern) is NULL, the result will be NULL as well.

Examples


[1] Returns the indices where the pattern like occurs.

Query
Column1
PATINDEX ( "Table1"."Column1" , '%like%' )
Input
Table1
Column1 : STRING
'I like database systems'
null
'Moves like Jagger'
'Process mining is awesome'
''
'Do you also like PQL?'
Output
Result
Column1 : INT
3
null
7
0
0
13



[2] Having a wildcard in the pattern is also supported.

Query
Column1
PATINDEX ( "Table1"."Column1" , '%da__base%syst_%' )
Input
Table1
Column1 : STRING
'I like database systems'
Output
Result
Column1 : INT
8



[3] Using the PATINDEX operator with two columns as input instead of constants for the pattern.

Query
Column1
PATINDEX ( "Table1"."Haystack" , "Table1"."Needle" )
Input
Table1
Haystack : STRINGNeedle : STRING
'I like database systems''%database%'
null'null'
'Moves like Jagger''%gg%'
'Process mining is awesome''%_mining_%'
''''
'Do you also like PQL?''%SQL%'
Output
Result
Column1 : INT
8
null
14
8
1
0



[4] To use the wildcard symbol as an actual character, the wildcard symbol must be escaped.

Query
Column1
PATINDEX ( "Table1"."Column1" , '%l\\%%d\\_t\\_bas_s' , 2 )
Input
Table1
Column1 : STRING
'I l%ke d_t_bases, I l%ke d_t_bases'
Output
Result
Column1 : INT
21



[5] Returns the indices where the pattern like occurs the n-th time.

Query
Column1
PATINDEX ( "Table1"."Column1" , '%like%' , 2 )
Input
Table1
Column1 : STRING
'I like database systems'
null
'Moves like Jagger'
'Process mining is awesome'
''
'Do you also like PQL, like, really, like something like'
Output
Result
Column1 : INT
0
null
0
0
0
23



[6] Returns the indices where the pattern like occurs the n-th time.

Query
Column1
PATINDEX ( "Table1"."Column1" , '%like%' , 4 )
Input
Table1
Column1 : STRING
'I like database systems'
null
'Moves like Jagger'
'Process mining is awesome'
''
'Do you also like PQL, like, really, like something like'
Output
Result
Column1 : INT
0
null
0
0
0
52



[7] Returns 0 if there is no n-th occurrence of the pattern.

Query
Column1
PATINDEX ( "Table1"."Column1" , '%like%' , 5 )
Input
Table1
Column1 : STRING
'I like database systems'
null
'Moves like Jagger'
'Process mining is awesome'
''
'Do you also like PQL, like, really, like something like'
Output
Result
Column1 : INT
0
null
0
0
0
0


  • No labels