REPLACE
Replaces all occurrences of a substring in a string with another substring.
MySQL 8.1
REPLACE(str, from_str, to_str)Parameters
- strVARCHAR
- Source string
- from_strVARCHAR
- Substring to replace
- to_strVARCHAR
- Substring inserted instead of from_str
Return value
A string with occurrences replaced; the search is case-sensitive; the original string if from_str is empty
Examples
Replacing a word in a string
MySQL 8.1
SELECT REPLACE('Hello world', 'world', 'SQL');Case-sensitive replacement
MySQL 8.1
SELECT REPLACE('SQL sql Sql', 'sql', 'X');Shortening the surname in names
MySQL 8.1
SELECT member_name,
REPLACE(member_name, 'Quincey', 'Q.') AS short_name
FROM FamilyMembers;Details
The case is always taken into account, even if regular string comparison in the database ignores it. The search goes from left to right without looking at already replaced text again: REPLACE('aaa', 'aa', 'b') returns ba. To replace by a pattern, use REGEXP_REPLACE.