<< RDB$SET_CONTEXT() | FB 2.5 Language Reference | REVERSE() >>
<< RDB$SET_CONTEXT() | FB 2.1 Language Reference | REVERSE() >>

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, any 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.

See also:
OVERLAY()

back to top of page
<< RDB$SET_CONTEXT() | FB 2.5 Language Reference | REVERSE() >>
<< RDB$SET_CONTEXT() | FB 2.1 Language Reference | REVERSE() >>