How to Replace Substrings in PostgreSQL

PostgreSQL provides powerful functions to manipulate strings, and one of the most common operations is replacing substrings within a string. Whether you're cleaning up data or modifying text for presentation, PostgreSQL has you covered. Let's explore how you can replace substrings using the built-in functions REPLACE and REGEXP_REPLACE.

Using the REPLACE Function

The REPLACE function in PostgreSQL allows you to replace all occurrences of a substring within a string. The syntax is simple:

REPLACE(string, old_substring, new_substring);

For example, if you want to replace all instances of 'apple' with 'orange' in a given string, you can do:

SELECT REPLACE('I love apple pie!', 'apple', 'orange');

Output:

I love orange pie!

Using the REGEXP_REPLACE Function

If you need to replace substrings based on patterns (regular expressions), the REGEXP_REPLACE function is your go-to solution. The syntax for this function is:

REGEXP_REPLACE(string, pattern, replacement [, flags]);

For instance, if you want to replace any sequence of digits in a string with asterisks, you can use the following query:

SELECT REGEXP_REPLACE('My phone number is 123-456-7890.', '\d', '*', 'g');

Output:

My phone number is ***-***-****.

When to Use Each Function

Use REPLACE when you know the exact substring you need to replace. It's fast and efficient for straightforward replacements. On the other hand, REGEXP_REPLACE should be used when you need to replace patterns or when you require more advanced control over the replacement process, such as case sensitivity or replacing only specific instances in a string.

Conclusion

Replacing substrings in PostgreSQL is easy with either REPLACE or REGEXP_REPLACE, depending on your needs. These functions make string manipulation in PostgreSQL more flexible and powerful, especially when working with large datasets or text-based applications.