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, dead URLs, or spelling mistakes. To search and replace all occurrences of a string with a new one, you use PostgreSQL replace function.
The following illustrates the syntax of the PostgreSQL replace function:
1 | REPLACE(source, old_text, new_text ); |
The replace function accepts three parameters:
- source is a string where you want to replace.
- old_text is 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_text is the new text that will replace the old text (
old_text).
PostgreSQL REPLACE function examples
See the following examples of using the replace function:
1 2 | SELECT REPLACE ('ABC AA', 'A', 'Z'); |
1 2 3 4 5 6 | SELECT REPLACE ( 'http://www.postgresqltutorial.com', 'tt', 'xx' ); |
If you want to search and replace character in field of a table, you use the following syntax:
1 2 3 | UPDATE table SET column = REPLACE(column,old_text,new_text) WHERE condition |
Let’s check the customer table:
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 | UPDATE customer SET email = REPLACE ( email, 'sakilacustomer.org', 'postgresqltutorial.com' ); |
Because we omit the WHERE clause, all rows are updated. Let’s verify the replacements:
1 2 3 4 5 6 | SELECT first_name, last_name, email FROM customer; |
PostgreSQL regex_replace function
In case you need a more advanced matching, you can use PostgreSQL regex_replace function. The regex_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 regex_replace function accepts four parameters:
- source is the string that you will look for substrings that match the
patternand replace it with thenew_text. If no match found, the source is unchanged. - pattern is a regular expression. It could be any patterns, for example: email, URL, phone number, etc.
- next_text is the text that replaces the substrings.
- flags parameter contains zero or more single-letter flag that control the behavior of the
regex_replacefunction. 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 regex_replace function examples
It takes efforts and experiments to understand how the regex_replace function works. The following are examples of using the regex_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 no 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 begins 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 means match at both beginning and end of each word. All words that begins and / or ends 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 regex_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:
- source is the string that you want to search and replace.
- set is a set of characters that is used for matching.
- new_set is a set of characters that replace the characters that match the
set.
Notice that of the set is longer than new_set, PostgreSQL removes the extra characters from the set.
PostgreSQL regex_replace 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, replace_regex, and translate to search and replace a substring with a new one.