August 1, 2023
Summary: in this tutorial, we will introduce you to the PostgreSQL replace functions that search and replace a substring with a new substring in a string.
PostgreSQL REPLACE function
Sometimes, you want to search and replace a string in a column with a new one such as replacing outdated phone numbers, broken URLs, and spelling mistakes.
To search and replace all occurrences of a string with a new one, you use the
The following illustrates the syntax of the PostgreSQL
REPLACE(source, old_text, new_text );
REPLACE() function accepts three arguments:
sourceis a string where you want to replace.
old_textis the text that you want to search and replace. If the
old_textappears multiple times in the string, all of its occurrences will be replaced.
new_textis the new text that will replace the old text (
REPLACE() function examples
See the following example of using the
SELECT REPLACE ('ABC AA', 'A', 'Z');
In this example, we replaced all characters ‘A’ with the character ‘Z’ in a string.
The following example replaces the substring
xx in a URL:
SELECT REPLACE ( 'https://www.rockdata.net', 'tt', 'xx' );
If you want to search and replace a substring in a table column, you use the following syntax:
UPDATE table_name SET column_name = REPLACE(column,old_text,new_text) WHERE condition
Let’s use the
customer table in the sample database for the demonstration:
SELECT first_name, last_name, email FROM customer;
Now, suppose you want to update the email column to replace the domain
rockdata.net, you use the following statement:
UPDATE customer SET email = REPLACE ( email, 'sakilacustomer.org', 'rockdata.net' );
Because we omitted the
WHERE clause, all rows in the
customer table were updated.
Let’s verify if the replacements have taken place.
SELECT first_name, last_name, email FROM customer;
PostgreSQL REGEXP_REPLACE function
In case you need a more advanced matching, you can use the
REGEXP_REPLACE() function allows you to replace substrings that match a regular expression. The following illustrates the syntax of the
REGEXP_REPLACE(source, pattern, new_text [,flags])
REGEXP_REPLACE() function accepts four parameters:
sourceis the string that you will look for substrings that match the
patternand replace it with the
new_text. If no match found, the source is unchanged.
patternis a regular expression. It could be any patterns, for example: email, URL, phone number, etc.
next_textis the text that replaces the substrings.
flagscontains zero or more single-letter flag that controls the behavior of the
REGEX_REPLACE()function. For example,
imeans case-insensitive matching or ignore case. g stands for global; If g flag is used, the function replaces all occurrences of substrings that match the pattern. The flags parameter is optional.
REGEXP_REPLACE() function examples
It takes efforts and experiments to understand how the
REGEXP_REPLACE() function works.
The following are examples of using the
SELECT regexp_replace( 'foo bar foobar barfoo', 'foo', 'bar' );
bar bar foobar barfoo
In the following example, because we use
i flag, it ignores case and replaces the first occurrence of
SELECT regexp_replace( 'Bar foobar bar bars', 'Bar', 'foo', 'i' );
foo foobar bar bars
In the following example, we use
g flag, all the occurrences of
bar is replaced by
foo. Notice that
bAR will not be changed.
SELECT regexp_replace( 'Bar sheepbar bar bars barsheep', 'bar', 'foo', 'g' );
Bar sheepfoo foo foos foosheep
In the following example, we use both
i flags, so all occurrences of
BAR, etc., are replaced by
SELECT regexp_replace( 'Bar sheepbar bar bars barsheep', 'bar', 'foo', 'gi' );
foo sheepfoo foo foos foosheep
\m means match only at the beginning of each word. All words that begin with
bar in whatever case are replaced by foo. Word ends with
bar are not changed.
SELECT regexp_replace( 'Bar sheepbar bar bars barsheep', '\mbar', 'foo', 'gi' );
foo sheepbar foo foos foosheep
\M means match only at the end of each word. All words, which end with
bar in whatever cases, are replaced by foo. Words begin with
bar will not be replaced.
SELECT regexp_replace( 'Bar sheepbar bar bars barsheep', 'bar\M', 'foo', 'gi' );
foo sheepfoo foo bars barsheep
\M mean matching at both the beginning and end of each word. All words that begin and/or end with
bar in whatever cases are replaced by
SELECT regexp_replace( 'Bar sheepbar bar bars barsheep', '\mbar\M', 'foo', 'gi' );
foo sheepbar foo bars barsheep
PostgreSQL TRANSLATE function
REGEXP_REPLACE() functions, PostgreSQL provides you with another function named
TRANSLATE() for string substitution.
setof characters, the
TRANSLATE() function replaces any characters in the
source string that match the
set with the characters in the
TRANSLATE(source, set, new_set);
TRANSLATE() function accepts three parameters:
sourceis the string that you want to search and replace.
setis a set of characters that is used for matching.
new_setis a set of characters that replace the characters that match the
Notice that if the
set has more characters than the
new_set, PostgreSQL removes the extra characters in the
set from the
PostgreSQL TRANSLATE example
In the following example, we translate all special vowels to the normal ones.
SELECT TRANSLATE ( 'LÒ BÓ VÔ XÕ', 'ÒÓÔÕ', 'OOOO' );
LO BO VO XO
See the following picture.
In this tutorial, we have shown you various functions:
TRANSLATE() to search and replace a substring with a new one.