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
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook