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 REPLACE()
function.
The following illustrates the syntax of the PostgreSQL REPLACE()
function:
REPLACE(source, old_text, new_text );
The REPLACE()
function accepts three arguments:
source
is a string where you want to replace.old_text
is the text that you want to search and replace. If theold_text
appears 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 example of using the REPLACE()
function:
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:
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 sakilacustomer.org
with 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.
The REGEXP_REPLACE()
function allows you to replace substrings that match a regular expression. The following illustrates the syntax of the REGEX_REPLACE()
function.
REGEXP_REPLACE(source, pattern, new_text [,flags])
The REGEXP_REPLACE()
function accepts four parameters:
source
is the string that you will look for substrings that match thepattern
and 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
contains zero or more single-letter flag that controls the behavior of theREGEX_REPLACE()
function. For example,i
means 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.
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 Bar
or bar
with foo
.
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
, BAR
, or 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 g
and i
flags, so all occurrences of bar
or Bar
, BAR
, etc., are replaced by foo
.
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
and \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 foo
.
SELECT
regexp_replace(
'Bar sheepbar bar bars barsheep',
'\mbar\M',
'foo',
'gi'
);
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 set
of characters, the TRANSLATE()
function replaces any characters in the source
string that match the set
with the characters in the new_set
.
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 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.
SELECT
TRANSLATE (
'LÒ BÓ VÔ XÕ',
'ÒÓÔÕ',
'OOOO'
);
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.