meta data for this page
REPLACE()
Added in: 2.1
Description
Replaces all occurrences of a substring in a string.
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.