REPLACE
Replaces all occurrences of the substring from in a string with the substring to.
PostgreSQL 17.5
REPLACE(string, from, to)Parameters
- stringtext
- Source string
- fromtext
- Substring to replace
- totext
- Replacement substring, an empty string removes the occurrences
Return value
A string with occurrences replaced; the search is case-sensitive
Examples
Replacing a substring
PostgreSQL 17.5
SELECT REPLACE('abcdef', 'cd', 'XX');Replacing all separators in a date
PostgreSQL 17.5
SELECT REPLACE('2024-03-08', '-', '.');Removing a word from names
PostgreSQL 17.5
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.