REPLACE()

`REPLACE()`

Replaces all occurrences of a substring in a string

Result type

VARCHAR or BLOB

Syntax

REPLACE (str, find, repl)

Table 1. `REPLACE` Function Parameters
Parameter	Description
str	The string in which the replacement is to take place
find	The string to search for
repl	The replacement 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.

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.

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'

REPLACE()

REPLACE()

REPLACE Examples

`REPLACE()`

`REPLACE` Examples