Skip to main content

PQL47 (PQL Function Library - CPM 4.7)

PATINDEX

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

Output

Table1

Column1 : STRING

'I like database systems'

null

'Moves like Jagger'

'Process mining is awesome'

''

'Do you also like PQL?'

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

Output

Table1

Column1 : STRING

'I like database systems'

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

Output

Table1

Haystack : STRING

Needle : STRING

'I like database systems'

'%database%'

null

'null'

'Moves like Jagger'

'%gg%'

'Process mining is awesome'

'%_mining_%'

''

''

'Do you also like PQL?'

'%SQL%'

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

Output

Table1

Column1 : STRING

'I l%ke d_t_bases, I l%ke d_t_bases'

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

Output

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'

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

Output

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'

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

Output

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'

Result

Column1 : INT

0

null

0

0

0

0