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.



PostgreSQL Version: 9.3

Pictorial Presentation of PostgreSQL REPLACE() function

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'.


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

Sample Output:

 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.


SELECT employee_id,job_id, 
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

Share this Tutorial / Exercise on : Facebook and Twitter