In PostgreSQL, to replace parts of a string within a column we will be using the REPLACE() function. The REPLACE() function takes three arguments: the original string, the substring to be replaced, and the substring to replace with. Let’s see how to
- Replace the string of column in PostgreSQL
- Replace specific part of the string
Syntax of REPLACE() function PostgreSQL:
REPLACE(string, from_substring, to_substring)
- string: The original string or column.
- from_substring: The substring to be replaced.
- to_substring: The substring to replace with.
Examples
Example 1: Replacing a Substring in a Specific String PostgreSQL
To replace the substring ‘SQL’ with ‘Database’ in the string ‘PostgreSQL’:
SELECT REPLACE('PostgreSQL', 'SQL', 'Database') AS replaced_string;
Output:
Example 2 : Replace the string of column in PostgreSQL:
We will be using Zipcodes table.
In order to replace the string of column in PostgreSQL we use REPLACE() function.
select *,REPLACE(city,'a','A') as Replace_city FROM zipcodes
Replace() function, replaces “a” with “A” in the city column. So the resultant table will be
Output:
