REPLACE()

Available in: DSQL, PSQL

Added in: 2.1

Description: Replaces all occurrences of a substring in a string.

Result type: VARCHAR or BLOB

Syntax:

REPLACE (str, find, repl)
This function fully supports text BLOBs of any length and character set.

If any argument is a BLOB, the result is a BLOB. Otherwise, the result is a VARCHAR(n) with n calculated from the lengths of str, find and repl in such a way that even the maximum possible number of replacements won't overflow the field.

If find is the empty string, str is returned unchanged.

If repl is the empty string, all occurrences of find are deleted from str.

If any argument is NULL, the result is always NULL, even if nothing would have been replaced.

Examples:

replace ('Billy Wilder',  'il', 'oog')     -- returns 'Boogly Woogder'
replace ('Billy Wilder',  'il',    '')     -- returns 'Bly Wder'
replace ('Billy Wilder',  null, 'oog')     -- returns NULL
replace ('Billy Wilder',  'il',  null)     -- returns NULL
replace ('Billy Wilder', 'xyz',  null)     -- returns NULL (!)
replace ('Billy Wilder', 'xyz', 'abc')     -- returns 'Billy Wilder'
replace ('Billy Wilder',    '', 'abc')     -- returns 'Billy Wilder'

Warning

When used on a BLOB, this function may need to load the entire object into memory. This may affect performance if huge BLOBs are involved.