PostgreSQL find and replace function

Summary : In this tutorial, we will introduce you to PostgreSQL replacement functions, which search for substrings in strings and replace them with new substrings.

PostgreSQL REPLACE function

Sometimes, you want to search for a string in a column and replace it with a new string, such as replacing outdated phone numbers, broken URLs, and spelling errors.

To search for all matches of a string and replace it with a new string, use the REPLACE() function.

The following illustrates the syntax of REPLACE()

REPLACE(source, old_text, new_text );

REPLACE() function accepts three parameters:

• source is a string you want to replace.
• old_text is the text you want to search and replace. If old_text appears multiple times in the string, all occurrences of it will be replaced.
• new_text is the new text that will replace the old text ( old_text

PostgreSQL REPLACE() function example

See the following example of using the "REPLACE()" function:

SELECT
    REPLACE ('ABC AA', 'A', 'Z');

In this example, we replace all the characters "A" in the string with the character "Z".

The following example replaces the substring "tt" in the URL with "xx":

SELECT
    REPLACE (
        'https://www.zhaoxinsoft.com',
        'tt',
        'xx'
    );

If you want to search and replace a substring in a table column, 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 to demonstrate:

SELECT
    first_name,
    last_name,
    email
FROM
    customer;

Now, suppose you want to update e-mail columns with zhaoxinsoft.com replacement field sakilacustomer.org , please use the following statement:

UPDATE 
   customer
SET 
   email = REPLACE (
      email,
    'sakilacustomer.org',
    'zhaoxinsoft.com'
   );

Because we omitted the WHERE clause, we updated all the rows in customer

Let us verify whether a replacement has occurred.

SELECT
    first_name,
    last_name,
    email
FROM
    customer;

PostgreSQL REGEXP_REPLACE function

If you need a more advanced matching method, you can use the REGEXP_REPLACE() function.

REGEXP_REPLACE() function allows you to replace the substring that matches the regular expression. The following explains the syntax of REGEX_REPLACE()

REGEXP_REPLACE(source, pattern, new_text [,flags])

REGEXP_REPLACE() function accepts four parameters:

• source is the string where you will find the pattern matches 061c15f62b2fdd and replace it with new_text . If no match is found, the source remains unchanged.
• pattern is a regular expression. It can be any mode, such as: email, URL, phone number, etc.
• next_text is the text to replace the substring.
• flags contains zero or more single-letter signs to control the behavior of the REGEX_REPLACE() For example, i means case-insensitive matching or ignoring case. g stands for global; if the g flag is used, this function will replace all occurrences of substrings that match the pattern. The flags parameter is optional.

PostgreSQL REGEXP_REPLACE() function example

It takes hard work and experimentation to understand the working principle of the REGEXP_REPLACE()

The following is an example 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 mark, it ignores case and with foo replace the first occurrence of Bar or bar .

SELECT
    regexp_replace(
        'Bar foobar bar bars',
        'Bar',
        'foo',
        'i'
    );

foo foobar bar bars

In the example below, we use the g flag, and all occurrences of bar are replaced with foo . Please note that Bar , BAR or bAR will not change.

SELECT
    regexp_replace(
        'Bar sheepbar bar bars barsheep',
        'bar',
        'foo',
        'g'
    );

Bar sheepfoo foo foos foosheep

In the example below, we use both g and i flags, so all occurrences of bar or Bar , BAR , etc. are replaced with foo .

SELECT
    regexp_replace(
        'Bar sheepbar bar bars barsheep',
        'bar',
        'foo',
        'gi'
    );

foo sheepfoo foo foos foosheep

\m means only match the beginning of each word. In any case, all bar are replaced by foo. bar ending in 061c15f62b3167 have not changed.

SELECT
    regexp_replace(
        'Bar sheepbar bar bars barsheep',
        '\mbar',
        'foo',
        'gi'
    );

foo sheepbar foo foos foosheep

\M means only match at the end of each word. All words ending in "bar" are replaced by foo. bar beginning with 061c15f62b31e6 will not be replaced.

SELECT
    regexp_replace(
        'Bar sheepbar bar bars barsheep',
        'bar\M',
        'foo',
        'gi'
    );

foo sheepfoo foo bars barsheep

\m and \M indicate a match at the beginning and end of each word. In any case, all bar beginning and ending foo .

SELECT
    regexp_replace(
        'Bar sheepbar bar bars barsheep',
        '\mbar\M',
        'foo',
        'gi'
    );

foo sheepbar foo bars barsheep

PostgreSQL TRANSLATE function

In addition to the REPLACE() and REGEXP_REPLACE() functions, PostgreSQL also provides another TRANSLATE() for string replacement.

Given a set character, TRANSLATE() function with new_set alternative characters source string matches set any character.

TRANSLATE(source, set, new_set);

The TRANSLATE() function accepts three parameters:

• source is the string you want to search and replace.
• set is a set of characters used for matching.
• new_set is a set of characters used to replace characters that match set

Please note that if set more characters than new_set , PostgreSQL will delete the extra characters set source

PostgreSQL TRANSLATE function example

In the following example, we convert all special vowels to normal vowels.

SELECT
    TRANSLATE (
        'LÒ BÓ VÔ XÕ',
        'ÒÓÔÕ',
        'OOOO'
    );

LO BO VO XO

Please see the picture below.

In this tutorial, we have shown you various functions: REPLACE() , REGEXP_REPLACE() and TRANSLATE() used to search for substrings and replace them with new substrings.