8.3.12. REPLACE()

Available in

DSQL, PSQL

Syntax

REPLACE (str, find, repl)

Result type

VARCHAR or BLOB

Description

Replaces all occurrences of a substring in a string.

• 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'

See also

OVERLAY(), SUBSTRING(), POSITION(), CHAR_LENGTH(), CHARACTER_LENGTH()