PostgreSQL REPLACE Function

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 );
Code language: SQL (Structured Query Language) (sql)

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 the old_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');
Code language: SQL (Structured Query Language) (sql)

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.postgresqltutorial.com',
		'tt',
		'xx'
	);
Code language: SQL (Structured Query Language) (sql)

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
Code language: SQL (Structured Query Language) (sql)

Let’s use the customer table in the sample database for the demonstration:

SELECT
	first_name,
	last_name,
	email
FROM
	customer;
Code language: SQL (Structured Query Language) (sql)

Now, suppose you want to update the email column to replace the domain sakilacustomer.org with postgresqltutorial.com, you use the following statement:

UPDATE 
   customer
SET 
   email = REPLACE (
  	email,
	'sakilacustomer.org',
	'postgresqltutorial.com'
   );
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

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])
Code language: SQL (Structured Query Language) (sql)

The REGEXP_REPLACE() function accepts four parameters:

•  source is the string that you will look for substrings that match the pattern and replace it with the new_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 the REGEX_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'
	);
Code language: SQL (Structured Query Language) (sql)

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'
	);
Code language: SQL (Structured Query Language) (sql)

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'
	);
Code language: SQL (Structured Query Language) (sql)

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'
	);
Code language: SQL (Structured Query Language) (sql)

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'
	);
Code language: SQL (Structured Query Language) (sql)

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'
	);
Code language: SQL (Structured Query Language) (sql)

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'
	);
Code language: SQL (Structured Query Language) (sql)

foo sheepbar foo bars barsheep
Code language: SQL (Structured Query Language) (sql)

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.

TRANSLATE(source, set, new_set);
Code language: SQL (Structured Query Language) (sql)

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 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'
	);
Code language: SQL (Structured Query Language) (sql)

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.