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 URL s, and spelling errors.

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

The following describes the syntax of the PostgreSQL REPLACE() function:

REPLACE(source, old_text, new_text );

The REPLACE() function accepts three parameters:

• source is a string you want to replace.
• old_text is the text you want to search for and replace. If old_text appears more than once in the string, and all occurrences of it will be replaced.
• new_text is the new text that will replace the 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 for and replace substrings in table columns, use the following syntax:

UPDATE 
   table_name
SET 
   column_name = REPLACE(column,old_text,new_text)
WHERE 
   condition

Let's demonstrate using the customer table in the sample database:

SELECT
    first_name,
    last_name,
    email
FROM
    customer;

Now, suppose you want to update the email column to use zhaoxinsoft Com replacement domain sakilacustomer.com Org, please use the following statement:

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

Because we omitted WHERE Clause, so all rows in the customer table are updated.

Let's verify that a replacement has occurred.

SELECT
    first_name,
    last_name,
    email
FROM
    customer;

PostgreSQL REGEXP_REPLACE function

If you need more advanced matching methods, you can use REGEXP_REPLACE() function.

REGEXP_ The replace() function allows you to replace substrings that match regular expressions. Regex is described below_ The syntax of the replace() function.

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

REGEXP_ The replace() function accepts four parameters:

• Source is the substring that you will find matching pattern and replace with new_text. If no match is found, the source remains unchanged.
• pattern is a regular expression. It can be any mode, such as e-mail, URL, phone number, etc.
• next_text is the text that replaces the substring.
• Flags contains zero or more single letter flags to control regex_ The behavior of the replace() function. For example, i means case insensitive matching or ignoring case. g represents the overall situation; If you use the g flag, this function replaces all substrings that match the pattern. The flags parameter is optional.

PostgreSQL REGEXP_REPLACE() function example

It takes effort and experimentation to understand regexp_ How the replace() function works.

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 the i flag, it ignores case and replaces the first 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 the g flag, and all Bar occurrences are replaced with foo. 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 following example, we use both the 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 to match only the beginning of each word. In any case, all words beginning with bar are replaced by foo. The word ending in bar has 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 ending in "bar" are replaced by foo. Words beginning with bar are not replaced.

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

foo sheepfoo foo bars barsheep

\M and \ m mean that they match at the beginning and end of each word. In any case, all words beginning and ending with bar are replaced with foo.

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

foo sheepbar foo bars barsheep

PostgreSQL TRANSLATE function

Except REPLACE() and REGEXP_REPLACE() function, PostgreSQL also provides another function called TRANSLATE() for string replacement.

Given a set character, the TRANSLATE() function uses new_ The character in set replaces any character in the source string that matches set.

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 for matching.
• new_set is a set of characters used to replace characters that match set.

Note that if the character of set is greater than new_ If there are too many sets, PostgreSQL will delete the extra characters in set from the source string.

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

Look at the picture below.

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