Postgres Regex Replace

Introduction

When you have data stored in PostgreSQL, you may encounter situations where you need to replace all occurrences of a certain string with a new string. The REPLACE() function makes it easy to accomplish that task. However, not all string replacements are that simple. Sometimes you need to find occurrences of a string that match a regular expression pattern instead of an exact string match. Fortunately, we can use the REGEXP_REPLACE() function in PostgreSQL to handle these situations with ease. In this article, we’ll show you how to use this function to perform a Postgres regex replace.

Prerequisite

Before moving forward with this tutorial, you’ll need to confirm that PostgreSQL is installed and configured on your machine.

What is Postgres REPLACE?

The Postgres REPLACE() function allows us to find occurrences of a specified string and replace them with a new string or set of strings.

Shown below is the basic syntax for the Postgres REPLACE() function:

1
REPLACE(<source>,<matching_text>,<new_text>);

Let’s take a closer look at this syntax:

  • The ‘source’ represents the string that we want to replace with a new value.
  • The ‘matching_text’ will be the text that we want to search for and replace. In the event that the ‘matching_text’ appears more than once within the source, all occurrences will be replaced.
  • The ‘new_text’ will be the text used to replace the old text that was specified in the ‘matching_text’.

Postgres REPLACE Example

Now that we have a better understanding of the Postgres REPLACE function, let’s look at how the function can be used.

Our first example can be seen below:

1
2
SELECT
REPLACE ('BATMAN', 'B', 'C');

The output will look something like this:

1
2
3
4
replace
---------
CATMAN
(1 row)

We can see that we were able to replace the ‘B’ from the word ‘BATMAN’ with the character ‘C’. The resulting string has the value of ‘CATMAN’.

Let’s check out another example:

1
2
SELECT
REPLACE ('MATTER', 'TT', 'LL');

This time we’re looking for a substring within the string ‘MATTER’. The substring we’re looking for is ‘TT’. This substring is then replaced that with ‘LL’, resulting in a new string with a value of ‘MALLER’.

Postgres REGEX_REPLACE example

In the previous section, we discussed the Postgres REPLACE() function. Now, let’s take a look at the REGEX_REPLACE() function. This function enables more advanced string matching than the REPLACE() function, allowing us to replace a string based on a match against a defined regular expression.

Shown below is the syntax of the Postgres REGEX_REPLACE() function:

1
REGEXP_REPLACE(<source>, <string_pattern>, <new_text> [,flags]);
  • The ‘source’ represents the string that will be matched against the pattern. In the event that no matches are found, the source will not be modified.
  • The ‘string_pattern’ represents our regular expression. Regular expressions are often used to form patterns such as phone numbers, URLs, emails and more.
  • The ‘new_text’ represents the text that will replace the substring from the source.
  • The ‘flags’ can be empty, or they may contain letters that tell PostgreSQL how the REGEX_REPLACE() function will behave. Shown below are some of the flags that we might use with this function:

  • ‘c’ – case-sensitive matching

  • ‘i’ – case-insensitive matching
  • ‘n’ – newline-sensitive matching
  • ‘p’ – partial-newline matching
  • ‘w’ – inverse partial newline-sensitive matching

Let’s use this syntax and apply it to a real-life example:

1
2
3
4
5
6
SELECT
regexp_replace(
'The quick brown rabbit jumps over the lazy dog',
'rabbit',
'fox'
);

In this example, we want to replace the word ‘rabbit’ with a ‘fox’. Our output should look something like this:

1
2
3
4
regexp_replace
---------------------------------------------
The quick brown fox jumps over the lazy dog
(1 row)

Next, let’s look at an example that makes use of the flags available for the REGEX_REPLACE() function:

1
2
3
4
5
6
7
SELECT
regexp_replace(
'The quick brown RABBIT jumps over the lazy dog',
'RABBIT',
'rabbit',
'i'
);

The output will look something like this:

1
2
3
4
regexp_replace
------------------------------------------------
The quick brown RABBIT jumps over the lazy dog
(1 row)

Notice that the Postgres REGEX_REPLACE function uses the ‘i’ flag– this flag ignores the case of the string and will replace the string with a new one.

Conclusion

When you need to search for strings that match a particular regular expression pattern, the Postgres REGEX_REPLACE function can help you accomplish the task. With this function, you can search for substrings that match your specified pattern and replace those occurrences with new text. In this article, we looked at a few examples of the Postgres REGEX_REPLACE function and learned how to use it. If you’ve followed along with our examples, you’ll be prepared to use this function to find and replace string data in your own PostgreSQL database.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.