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 REPLACE() function.
The following illustrates the syntax of the PostgreSQL REPLACE() function:
1 | REPLACE(source, old_text, new_text ); |
The 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 theold_textappears multiple times in the string, all of its occurrences will be replaced. -
new_textis the new text that will replace the old text (old_text).
PostgreSQL REPLACE() function examples
See the following example of using the REPLACE()function:
1 2 | 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 tt with xx in a URL:
1 2 3 4 5 6 | SELECT REPLACE ( 'http://www.postgresqltutorial.com', 'tt', 'xx' ); |
If you want to search and replace a substring in a table column, you use the following syntax:
1 2 3 4 5 6 | 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:
1 2 3 4 5 6 | SELECT first_name, last_name, email FROM customer; |
Now, suppose you want to update the email column to replace the domain sakilacustomer.org with postgresqltutorial.com, you use the following statement:
1 2 3 4 5 6 7 8 | UPDATE customer SET email = REPLACE ( email, 'sakilacustomer.org', 'postgresqltutorial.com' ); |
Because we omitted the WHERE clause, all rows in the customer table were updated.
Let’s verify if the replacements have taken place.
1 2 3 4 5 6 | 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.
The REGEXP_REPLACE() function allows you to replace substrings that match a regular expression. The following illustrates the syntax of the REGEX_REPLACE() function.
1 | REGEXP_REPLACE(source, pattern, new_text [,flags]) |
The REGEXP_REPLACE() function accepts four parameters:
-
sourceis the string that you will look for substrings that match thepatternand replace it with thenew_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 theREGEX_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.
PostgreSQL REGEXP_REPLACE() function examples
It takes efforts and experiments to understand how the REGEXP_REPLACE() function works.
The following are examples of using the REGEXP_REPLACE() function.
1 2 3 4 5 6 | SELECT regexp_replace( 'foo bar foobar barfoo', 'foo', 'bar' ); |
1 | bar bar foobar barfoo |
In the following example, because we use i flag, it ignores case and replaces the first occurrence of Bar or bar with foo.
1 2 3 4 5 6 7 | SELECT regexp_replace( 'Bar foobar bar bars', 'Bar', 'foo', 'i' ); |
1 | foo foobar bar bars |
In the following example, we use g flag, all the occurrences of bar is replaced by foo. Notice that Bar, BAR, or bAR will not be changed.
1 2 3 4 5 6 7 | SELECT regexp_replace( 'Bar sheepbar bar bars barsheep', 'bar', 'foo', 'g' ); |
1 | Bar sheepfoo foo foos foosheep |
In the following example, we use both g and i flags, so all occurrences of bar or Bar, BAR, etc., are replaced by foo.
1 2 3 4 5 6 7 | SELECT regexp_replace( 'Bar sheepbar bar bars barsheep', 'bar', 'foo', 'gi' ); |
1 | 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.
1 2 3 4 5 6 7 | SELECT regexp_replace( 'Bar sheepbar bar bars barsheep', '\mbar', 'foo', 'gi' ); |
1 | 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.
1 2 3 4 5 6 7 | SELECT regexp_replace( 'Bar sheepbar bar bars barsheep', 'bar\M', 'foo', 'gi' ); |
1 | foo sheepfoo foo bars barsheep |
\m and \M mean matching at both beginning and end of each word. All words that begin and/or end with bar in whatever cases are replaced by foo.
1 2 3 4 5 6 7 | SELECT regexp_replace( 'Bar sheepbar bar bars barsheep', '\mbar\M', 'foo', 'gi' ); |
1 | foo sheepbar foo bars barsheep |
PostgreSQL TRANSLATE function
Besides the REPLACE() and REGEXP_REPLACE() functions, PostgreSQL provides you with another function named TRANSLATE() for string substitution.
Given a setof characters, the TRANSLATE() function replaces any characters in the source string that match the set with the characters in the new_set.
1 | TRANSLATE(source, set, new_set); |
The 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 theset.
Notice that if the set has more characters than the new_set, PostgreSQL removes the extra characters in the set from the source string.
PostgreSQL TRANSLATE example
In the following example, we translate all special vowels to the normal ones.
1 2 3 4 5 6 | SELECT TRANSLATE ( 'LÒ BÓ VÔ XÕ', 'ÒÓÔÕ', 'OOOO' ); |
1 | LO BO VO XO |
See the following picture.
In this tutorial, we have shown you various functions: REPLACE(), REGEXP_REPLACE(), and TRANSLATE() to search and replace a substring with a new one.