PostgreSQL REPLACE() function

Last update on July 05 2024 11:44:10 (UTC/GMT +8 hours)

REPLACE() function

Overview

The REPLACE() function in PostgreSQL is a powerful tool for string manipulation. It allows you to replace all occurrences of a specified substring within a given string with another substring. This function is highly versatile and can be used for a wide range of text processing tasks, such as cleaning up data, formatting strings, and preparing text for display or storage.

Key Features of the REPLACE() Function

Substring Replacement: It replaces all instances of a specified substring within a string.

Versatility: Useful for both static and dynamic text modifications in SQL queries.

Ease of Use: Simple syntax makes it accessible for various text transformation tasks.

Syntax:

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

Parameters:

<input_string>: The original string where replacements will be made.

<substring_to_replace>: The substring within the input string that you want to replace.

<replacement_substring>: The new substring that will replace the old substring.

PostgreSQL Version: 9.3

Pictorial Presentation of PostgreSQL REPLACE() function

Simple Replacement

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

Code:


-- Replace 'st' with '**' in the given string 'test string'
SELECT replace('test string', 'st', '**');

Explanation:

This SQL statement demonstrates the use of the PostgreSQL REPLACE() function to substitute all occurrences of the substring 'st' with '**' in the string 'test string'.

As a result, the output string will have each 'st' replaced with '', transforming 'test string' into 'te **ring'.

Sample Output:

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

Replacing Substring in a 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 the columns 'employee_id' and 'job_id' from the 'employees' table
SELECT employee_id, job_id, 
-- Replace 'VP' with '***' in the 'job_id' column and return it as part of the result
replace(job_id, 'VP', '***')
-- Specify the source table as 'employees'
FROM employees 
-- Filter the rows to include only those employees whose salary is greater than 15000
WHERE salary > 15000;

Explanation:

This SQL query retrieves the employee_id, job_id, and a modified job_id for employees whose salary exceeds 15,000.

The REPLACE() function is applied to the job_id column to substitute all occurrences of the substring 'VP' with '***'.

This allows for a formatted display of job IDs where the substring 'VP' is replaced, demonstrating how string replacements can be performed directly within query results.

Sample Output:

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

Email Domain Correction

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 and apply the REPLACE() function to the 'p_email' column from the 'test' table
SELECT 
    -- Use the REPLACE() function to substitute 'kom' with 'com' in the 'p_email' column
    REPLACE(p_email, 'kom', 'com') AS new_domain
-- Specify the source table as 'test'
FROM test;

Explanation:

This SQL query selects email addresses from the p_email column in the test table and uses the REPLACE() function to substitute all occurrences of the substring 'kom' with 'com'.

The resulting modified email addresses are returned in a new column named new_domain.

This operation is useful for correcting or standardizing data within a column directly in the query results, such as fixing incorrect domain suffixes in email addresses.

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.

Multiple Replacements in One Query

Using REPLACE() multiple times to handle complex string changes. For example, if you need to replace '2020' with '2021' and 'old' with 'new' in a single string:

code:


-- Apply the first REPLACE() function to replace '2020' with '2021' in the given string
SELECT REPLACE(
    -- Apply the second REPLACE() function to replace 'old' with 'new' in the given string
    REPLACE('2020 old data', '2020', '2021'), 
    -- Now, replace 'old' with 'new' in the intermediate result
    'old', 'new'
) AS updated_string;

Explanation:

This SQL query uses nested REPLACE() functions to perform multiple replacements on the string '2020 old data':

Inner REPLACE() Function:

Replaces all occurrences of '2020' with '2021' in the string '2020 old data'.

The result of this operation is '2021 old data'.

Outer REPLACE() Function:

Takes the result from the inner REPLACE() and replaces 'old' with 'new'.

The result of this operation is '2021 new data'.

The final output, assigned an alias updated_string, is the modified string '2021 new data'.

Sample Output:

updated_string|
--------------+
2021 new data |

Combining with Other String Functions

Combining REPLACE() with other functions like LOWER(), UPPER(), or TRIM() for more advanced text processing:

code:


-- Apply the REPLACE() function to the result of the LOWER() and TRIM() functions
SELECT REPLACE(
    -- Convert the trimmed string to lowercase using the LOWER() function
    LOWER(
        -- Remove leading and trailing spaces from the string using the TRIM() function
        TRIM('  Test Replace  ')
    ),
    -- Specify the substring to be replaced ('test') and the replacement substring ('best')
    'test', 'best'
) AS modified_string;

Explanation:

This SQL query demonstrates the use of nested string functions to clean up and transform the input string ' Test Replace ':

TRIM() Function:

Removes leading and trailing spaces from the string ' Test Replace '.

The result of this operation is 'Test Replace'.

LOWER() Function:

Converts the trimmed string to all lowercase letters.

The result of this operation is 'test replace'.

REPLACE() Function:

Replaces all occurrences of the substring 'test' with 'best' in the lowercase string.

The result of this operation is 'best replace'.

The final output, assigned the alias modified_string, is the cleaned and modified string 'best replace'.

Sample Output:

modified_string|
---------------+
best replace   |

Comparison with Similar Functions

TRANSLATE()

Unlike REPLACE(), which substitutes whole substrings, TRANSLATE() performs character-by-character replacement. This can be useful for simpler tasks where multiple characters need to be replaced simultaneously.

REGEXP_REPLACE()

For more complex replacements involving patterns, REGEXP_REPLACE() is suitable. It uses regular expressions to match and replace text, offering greater flexibility for sophisticated string manipulation needs.

Frequently Asked Questions (FAQ) - PostgreSQL REPLACE() Function

1. What is the PostgreSQL REPLACE() function?

The REPLACE() function in PostgreSQL is used to substitute all occurrences of a specific substring within a string with another substring. It’s a versatile tool for various text processing tasks, such as cleaning, formatting, or standardizing data in SQL queries.

2. What are the key features of the REPLACE() function?

Substring Replacement: It efficiently replaces all instances of a given substring within a string.

Versatility: Useful for a wide range of static and dynamic text modifications.

Ease of Use: Simple syntax that is easy to apply for transforming text in SQL queries.

3. When would we use the REPLACE() function?

The REPLACE() function is ideal for scenarios where we need to update text by replacing certain patterns with new ones. Common use cases include:

Data Cleanup: Correcting or standardizing data entries, such as fixing typos in text fields.

String Formatting: Modifying strings to meet specific display or formatting requirements.

Data Transformation: Preparing strings for further processing or analysis by altering specific parts of the text.

4. How does the REPLACE() function handle nested replacements?

The REPLACE() function can be used multiple times in a query to handle complex string transformations. By nesting REPLACE() calls, you can perform multiple substitutions in a single statement, allowing for detailed and layered modifications to your text.

5. Can the REPLACE() function be combined with other functions?

Yes, the REPLACE() function can be combined with other string functions like LOWER(), UPPER(), or TRIM() to enhance text processing capabilities. This combination allows for more advanced manipulation, such as cleaning up text by trimming spaces, changing case, and replacing substrings all in one operation.

Previous: REPEAT function
Next: RPAD function