PostgreSQL REPLACE() function

REPLACE() function

The PostgreSQL REPLACE() function is used to replace all occurrences of a substring within a string with another substring. It takes three arguments: the input string, the substring to be replaced, and the replacement substring.

By specifying the substring to be replaced and the replacement substring, you can modify the contents of a column or variable according to your specific needs.

Syntax:

replace(<string>,<matching_string>,<replace_with>)

PostgreSQL Version: 9.3

Pictorial Presentation of PostgreSQL REPLACE() function

Example: PostgreSQL REPLACE() function:

In the example below, the specified string 'st' have been replaced by '**' in the string 'test string'.

Code:

SELECT replace('test string', 'st', '**');

Sample Output:

   replace
-------------
 te** **ring
(1 row)

Example of PostgreSQL REPLACE() function using column :

Sample Table: employees

If we want to display the employee_id, job_id and a formatted job_id which is including '***' instead of 'VP' from employees table for those employees, who drawn a salary of more than 15000 , the following SQL can be used.

Code:

SELECT employee_id,job_id, 
replace(job_id,'VP','***')
FROM employees 
WHERE salary>15000;

Sample Output:

 employee_id | job_id  | replace
-------------+---------+---------
         100 | AD_PRES | AD_PRES
         101 | AD_VP   | AD_***
         102 | AD_VP   | AD_***
(3 rows)

Example:

Table : test

p_name     |p_id          |p_email                |
-----------+--------------+-----------------------+
Peter Mont |PEMO-7894-OMEP|[email protected]  |
Derak Powel|DEPO-8529-OPED|[email protected]|

If we want to replace all occurrences of "kom" with "com" in the p_email column the following code can be used:

code:

SELECT REPLACE(p_email, 'kom', 'com') AS new_domain
FROM test;

Sample Output:

new_domain             |
-----------------------+
[email protected]  |
[email protected]|

In this example, we use the REPLACE() function to replace all occurrences of the substring 'kom' with 'com' in the p_email column. The result will be a list of p_email with the replacements applied.

Previous: REPEAT function
Next: RPAD function