PostgreSQL REPLACE() function

REPLACE() function

The PostgreSQL replace function is used to replace all occurrences of matching_string in the string with the replace_with_string.

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)

Previous: REPEAT function
Next: RPAD function