REPLACE
Replaces all occurrences of the substring from in a string with the substring to.
PostgreSQL 17.9
REPLACE(string, from, to)- string
- Source stringtext
- from
- Substring to replacetext
- to
- Replacement substring, an empty string removes the occurrencestext
Return value
A string with occurrences replaced; the search is case-sensitive
Examples
Replacing a substring
PostgreSQL 17.9
SELECT REPLACE('abcdef', 'cd', 'XX');Replacing all separators in a date
PostgreSQL 17.9
SELECT REPLACE('2024-03-08', '-', '.');Removing a word from names
PostgreSQL 17.9
SELECT good_name,
REPLACE(good_name, ' fee', '') AS short_name
FROM Goods
WHERE good_name LIKE '% fee';Details
The function looks for a plain substring, not a pattern: for regular expressions there is REGEXP_REPLACE, and for replacing single characters, TRANSLATE. An empty from changes nothing.