Skip to main content

Celonis Product Documentation

PATINDEX
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. If the n-th occurrence of the pattern exists, the start index of the n-th occurrence is returned. Otherwise, if the n-th occurrence of the pattern does not exist, 0 is returned. This happens when

    • the pattern is not found in the input string

    • the occurrence number is larger than the number of times the pattern occurs

    • the occurrence number is negative

NOTE: In general, this operator conforms to the behavior 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, pattern or occurrence) 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 the indices where the pattern like occurs the n-th time with overlapping pattern occurrences.

Query

Column1 

         PATINDEX ( "Table1"."Column1" , '%BB%' , 3 )

Input

Output

Table1

Column1 : string

'BaaBBaaaBBBBaaBB'

Result

Column1 : int

10

[8]

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