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
REPLACEoperator 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' )
[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
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!' |
Output
Result
| Column1 : STRING |
|---|
| 'I like database management systems' |
| null |
| 'Mick Jagger' |
| 'Process mining is awesome' |
| '' |
| 'Yes, of course!' |