PostgreSQL REPLACE Function

What is REPLACE Function in PostgreSQL?

Replace function in Postgresql is used for replacing all the occurrences of the particular substring in the string with another string. Three arguments are taken as a parameter by the replace function in PostgreSQL i.e. input string, the substring required to be replaced, and string used for replacement.

Syntax

The syntax for replace in Postgresql is given below:

Now let us understand the syntax given above:

• source is a string in which we want to perform a replacement operation.
• old_text is a string that is searched in the source string for the replacement. There can be multiple occurrences of old_text, every occurrence is replaced at the time of execution of replace().
• new_text: is a string by which all the occurrences of old_text are replaced.

Examples

Example 1:

Below is a query which replaces all the occurrences of string stu to the student.

Output:

Example 2:

Let us take an sample table of student which will store the rank of students. Student

Now we want fetch the data of this table, but we want that rank of first rank students will be replaced to toppers. So we can write below query.

Output:

Example 3:

Suppose we have table of employee details named employee which stores the employeeId and email. Employee

Now we will writea query to fetch the email of all employees with their employeeId and we want that .org will be displayed in place of .com in the result.

Output:

PostgreSQL REGEXP_REPLACE Function

The REGEXP_REPLACE() function is used for performing advanced matching for the replacement of substring within the string with another string. It by default replaces only the first occurrence of the substring with another string. This function allows to specify the regular expression for replacing the substring of the string. The syntax of REGEXP_REPLACE() is given below.

Syntax:

The following parameters are accepted by the REGEXP_REPLACE():

• source is a string in which we want to perform a replacement operation.
• pattern is a pattern for the string that is searched in the source string for the replacement.
• new_text is a string by which the occurrence of old_text is replaced.
• flags contains zero or more than zero alphabets for controlling the REGEXP_REPLACE() function behavior. And it is an optional parameter. Some examples of the flag are: g is used for global matching which means all the occurrences of old_text are replaced with new_text, and i is used to perform case-insensitive matching of the string.

Examples

The following are examples of using the REGEXP_REPLACE() function.

Example 1:

Below query will replace the first occurrence of stu with student.

Output:

Example 2:

Below is the query in which we have used the i flag to perform case insensitive matching of stu for replacing it with student.

Output

Example 3:

Below is an example in which we have used the g flag, which replaces all the occurrences of stu with student.

Output:

Example 4:

In the below example, we have used both the g and i flags simultaneously, so all the occurrences of stu, Stu, STU, etc. will be replaced with student.

Output: