Skip to main content

PQL47 (PQL Function Library - CPM 4.7)

REPLACE

Applies to: CELONIS 4.7

Description

REPLACE returns the strings with pattern-substrings replaced by the string replace.

It should be noted that not all occurrences of pattern are replaced. Consider the following string: ABABA. Replacing the pattern ABA by X will lead to the output XBA although ABA occurs twice. This is due to the fact, that the first replacement of ABA will prevent the occurrence of the next. This behaviour is identical to MSSQL's behaviour.

Supported input column types: STRING

Output column type: STRING

Syntax
REPLACE ( table.column, pattern, replace )

  • table.column: The string column on which the REPLACE operator is executed.

  • pattern: The pattern which is searched for in the input string.

  • replace: The replacement for a found pattern in the input string.

NULL handling

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

Examples

[1] Returns the strings with occurrences of like replaced by love.

Query

Column1

REPLACE ( "Table1"."Column1" , 'like' , 'love' )

Input

Output

Table1

Column1 : STRING

'I like database systems'

null

'Moves like Jagger'

'Process mining is awesome'

''

'Do you also like PQL?'

Result

Column1 : STRING

'I love database systems'

null

'Moves love Jagger'

'Process mining is awesome'

''

'Do you also love PQL?'

[2] Having an empty pattern does not modify the input at all.

Query

Column1

REPLACE ( "Table1"."Column1" , '' , 'love' )

Input

Output

Table1

Column1 : STRING

'I like database systems'

Result

Column1 : STRING

'I like database systems'

[3] Having an empty replacement does delete the pattern.

Query

Column1

REPLACE ( "Table1"."Column1" , 'like' , '' )

Input

Output

Table1

Column1 : STRING

'I like database systems'

Result

Column1 : STRING

'I database systems'

[4] Using the REPLACE operator with three columns as input instead of constants for the pattern and replacement.

Query

Column1

REPLACE ( "Table1"."Haystack" , "Table1"."Needle" , "Table1"."Replacement" )

Input

Output

Table1

Haystack : STRING

Needle : STRING

Replacement : STRING

'I like database systems'

'database'

'database management'

null

'null'

'null'

'Moves like Jagger'

'Moves like'

'Mick'

'Process mining is awesome'

'handsome'

'404: Pattern Not Found'

''

''

'Not an empty string'

'Do you also like PQL?'

'Do you also like PQL?'

'Yes, of course!'

Result

Column1 : STRING

'I like database management systems'

null

'Mick Jagger'

'Process mining is awesome'

''

'Yes, of course!'

See also: