Replace functions here to help you

In this article, I want to show you how to manipulate data with different types of replacements. Let’s say we have emails ending in ‘@gmail.com’, and we want to change them to ‘@yourcompanydomain.com’ or maybe something more complicated, like anything after ‘@’ to be ‘@yourcompanydomain.com’.

PostgreSQL provides several functions, so let’s look at some of them.

regexp_replace

The regexp_replace function, in my opinion, is the most effective tool in SQL. It handles complex pattern-based replacements using regular expressions, particularly when you need to replace specific patterns within a string.

In this example, we’re replacing everything after ‘@’ with @test.com in the email addresses stored in the customer table.

SELECT 
 email,
 regexp_replace(email, '@.*$', '@test.com') as modified_email
FROM customer;

regexp_replace(email, '@.*$', '@test.com'): This function call replaces any text that matches the pattern '@' followed by any characters (.*) until the end of the string ($).

We can quickly compare results with the original email vs the new adjusted column named modified_email.

split_part

I like this one, too. It’s good if you keep it in mind because it can help you in many other scenarios as well (remember the split data to columns in Excel? This is kind of something like that). However, for this approach, we will continue with the email column. With the split_part function, we can split the email address at the @ symbol and then concatenate the desired replacement string, in this case '@test.com'.

SELECT 
 email,
 split_part(email, '@', 1) || '@.test.com' as modified_email
FROM customer;

split_part(email, '@', 1): This extracts the part of the email before the @ symbol.

|| '@.test.com': This concatenates the extracted part with @.test.com.

And again, we can quickly compare results with the original email vs the new adjusted column named modified_email.

replace

This one is the simplest way to replace values in a string. This method is straightforward and works well for basic replacements. But you’ll see it gives you some limitations for the same reason that it is very straightforward. In this case, it will replace only the ‘@example.com’ with ‘@test.com’.

SELECT 
 email,
 replace(email, '@example.com', '@test.com') as modified_email
FROM customer;

Explanation:

replace(email, '@gmail', '@test'): This function call replaces every occurrence of gmail with test within the email addresses.

And one more time, we can quickly compare results with the original email column vs the new adjusted one modified_email. However, any other row that does not end with ‘@example.com’ will remain the same in the modified_email.

Recap

• regexp_replace : extremely useful. It can be intimidating because of the regular expressions it requires, but there’s a lot of documentation to get you where you need to be.
• split_part : pretty useful in my experience, not only concatenating but also extracting a part of a string that is valuable for data analysis.
• replace : very, very straightforward.

Thank you for reading! If you enjoyed this article, please consider supporting me in the following ways:

• Like this story 👏 as often as you like, or Leave a comment 💬 to share your thoughts and feedback.
• Follow me on Medium to stay updated with my latest articles.
• Follow me on X for daily programming tips and more!
• Buy me a coffee ☕️.
• Visit My Digital Store for PostgreSQL Tips, Ebooks, Excel Templates and more!

Your support means the world to me. Thank you!