Firebird Documentation IndexFirebird 2.1 Language Ref. UpdateInternal functions → REPLACE()
Firebird Home Firebird Home Prev: RDB$SET_CONTEXT()Firebird Documentation IndexUp: Internal functionsNext: 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, 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.

See also: OVERLAY()

Prev: RDB$SET_CONTEXT()Firebird Documentation IndexUp: Internal functionsNext: REVERSE()
Firebird Documentation IndexFirebird 2.1 Language Ref. UpdateInternal functions → REPLACE()