PostgreSQL REGEXP_REPLACE Function

Summary: in this tutorial, you’ll learn how to use the PostgreSQL REGEXP_REPLACE function to replace substrings in a string based on a regular expression.

PostgreSQL REGEXP_REPLACE Function Overview #

The REGEXP_REPLACE function replaces substrings in a string based on a POSIX regular expression pattern.

Here’s the syntax of the REGEXP_REPLACE function:

REGEXP_REPLACE(source_string, pattern, replacement [, flags])Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The REGEXP_REPLACE function takes four parameters:

• string: The input string you want to replace a pattern.
• pattern: A POSIX regular expression to match.
• replacement: The string used to replace matches.
• flags (optional): is a modifier that controls the function that performs a regular expression match. For example, 'i' for case-insensitive matching and 'g' for global replacement.

PostgreSQL REGEXP_REPLACE function examples #

Let’s take some examples of using the REGEXP_REPLACE function.

Removing All Non-Numeric Characters #

The following example uses the REGEXP_REPLACE function to extract only numbers from a string with mixed numbers and strings:

SELECT REGEXP_REPLACE('Order: 123-ABC-456', '[^0-9]', '', 'g') result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 result
--------
 123456Code language: plaintext (plaintext)

Standardizing Phone Numbers #

The following example uses the REGEXP_REPLACE function to format the phone number in the following format (123) 456-7890 :

SELECT
  REGEXP_REPLACE(
    '1234567890',
    '(\d{3})(\d{3})(\d{4})',
    '(\1) \2-\3'
  ) phone_number;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

  phone_number
----------------
 (123) 456-7890Code language: plaintext (plaintext)

Removing redundant whitespace #

The following example uses the REGEXP_REPLACE function to normalize multiple spaces into a single space:

SELECT
  REGEXP_REPLACE(
    'Hello    World!  This  is pgtutoiral.com.',
    '\s+',
    ' ',
    'g'
  ) message;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

               message
--------------------------------------
 Hello World! This is pgtutoiral.com.Code language: plaintext (plaintext)

Censoring Offensive Words #

The following example uses the REGEXP_REPLACE function to replace variations of offensive words while preserving part of them:

SELECT
  REGEXP_REPLACE(
    'This tutorial is damn good.',
    'damn',
    'd***',
    'gi'
  ) message;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

           message
-----------------------------
 This tutorial is d*** good.Code language: plaintext (plaintext)

Summary #

• Use the REGEXP_REPLACE function for regular expression pattern-based replacements.