REGEXP_REPLACE

Replace substrings matching a regular expression with a replacement that may reference captured groups.

Category: regexReturns: VARCHARDialect: PostgreSql

Syntax

REGEXP_REPLACE(str, pattern, replacement [, flags])

Description

## Overview Searches the input string for matches of the regular expression and replaces them with the replacement string. By default, only the first match is replaced; pass 'g' in the flags argument to replace every non-overlapping occurrence. The replacement string may contain backreferences (\1 through \9, or the whole-match shortcut \0 in some dialects) to insert text captured by groups in the pattern. REGEXP_REPLACE is the workhorse for pattern-based string cleanup: stripping formatting, normalising whitespace, reformatting dates and phone numbers, masking PII, and rewriting structured tokens. ## Behavior - Returns NULL when any argument is NULL. - Replaces only the first match unless the 'g' flag is present. - Backreferences \1, \2, ... refer to the corresponding capturing group in the pattern. A literal backslash in the replacement is \\. - An empty replacement deletes each match. - Zero-width matches advance one character at a time to ensure termination. - Pattern compilation errors raise an error at query time. - Operates on Unicode code points. ## Regex flavor - Rust regex crate, Perl-compatible for most common features. - Supports anchors, character classes, alternation, quantifiers, capturing groups (numbered and named), Unicode classes, and escape sequences. - Does NOT support backreferences in the pattern (the replacement string's \N is fine; pattern-internal \N is not). - Does NOT support lookahead or lookbehind. - Flags: 'g' global, 'i' case-insensitive, 'c' case-sensitive (default), 'n'/'s' dot-matches-newline, 'm' multi-line. ## Compatibility - Matches the common SQL REGEXP_REPLACE semantics for its 3/4-argument forms.

Parameters

Name	Type	Description
`str`		Specifies the input string in which replacements are performed.
`pattern`		Specifies the regular expression pattern to search for.
`replacement`		Specifies the replacement string. Use backreferences (\1, \2, and so on) to insert text captured by groups in the pattern. A literal backslash is written as \\.
`flags`		Specifies match flags. Include 'g' to replace all occurrences. Other flags: 'i' (case-insensitive), 'c' (case-sensitive, default), 'n'/'s' (dot matches newline), 'm' (multi-line).

Examples

-- Replace the first occurrence only (no 'g' flag)
SELECT REGEXP_REPLACE('abc123def456', '[0-9]+', 'NUM');  -- 'abcNUMdef456'

-- Replace every occurrence with 'g'
SELECT REGEXP_REPLACE('abc123def456', '[0-9]+', 'NUM', 'g');  -- 'abcNUMdefNUM'

-- Reorder a date using backreferences
SELECT REGEXP_REPLACE('2025-04-03', '(\d{4})-(\d{2})-(\d{2})', '\3/\2/\1');  -- '03/04/2025'

-- Collapse runs of whitespace to a single space
SELECT REGEXP_REPLACE('hello   world  !', '\s+', ' ', 'g');  -- 'hello world !'

-- Case-insensitive global replacement
SELECT REGEXP_REPLACE('Hello hello HELLO', 'hello', 'hi', 'gi');  -- 'hi hi hi'

-- NULL propagates
SELECT REGEXP_REPLACE(CAST(NULL AS VARCHAR), 'x', 'y');  -- NULL

Pitfalls

Without the 'g' flag, only the first match is replaced. This is the single most common REGEXP_REPLACE bug. Always think about whether you mean one or many.
Backreferences in the replacement use backslash-N (\1, \2, ...). To emit a literal backslash, double it (\\). Some SQL clients further escape backslashes at the literal level, so you may need \\\\ in quoted strings.
Pattern-internal backreferences (for example (a)\1) are not supported by the underlying engine. Rewrite the pattern to avoid them or use a two-pass approach.
REGEXP_REPLACE is more expensive than REPLACE. For fixed-string substitution, prefer REPLACE; for character-set substitution, prefer TRANSLATE.