How to Use regexp_replace in PostgreSQL: Syntax and Examples

In PostgreSQL, use regexp_replace to search a string for a pattern using regular expressions and replace it with a new substring. The basic syntax is regexp_replace(source, pattern, replacement [, flags]), where you specify the text to search, the regex pattern, and the replacement text.

Syntax

The regexp_replace function replaces substrings matching a regular expression pattern in a source string with a replacement string.

• source: The original text to search.
• pattern: The regular expression pattern to find.
• replacement: The text to replace each match with.
• flags (optional): Modifiers like 'g' for global replacement (all matches), 'i' for case-insensitive matching.

sql

regexp_replace(source, pattern, replacement [, flags])

Example

This example replaces all digits in the string with the '#' character using the 'g' flag for global replacement.

sql

SELECT regexp_replace('Call me at 123-456-7890', '\\d', '#', 'g') AS replaced_text;

Output

replaced_text
---------------------
Call me at ###-###-####

Common Pitfalls

Common mistakes include forgetting to escape backslashes in the pattern, not using the 'g' flag to replace all matches, and misunderstanding regex syntax.

For example, regexp_replace('abc123', '\\d', 'X') replaces only the first digit. To replace all digits, add the 'g' flag.

sql

SELECT regexp_replace('abc123', '\\d', 'X') AS first_replace,
       regexp_replace('abc123', '\\d', 'X', 'g') AS all_replace;

Output

first_replace | all_replace
--------------+------------
abcX23        | abcXXX

Quick Reference

Parameter	Description	Example
source	Text to search	'hello123'
pattern	Regex pattern to find	'\\d' (digits)
replacement	Text to replace matches	'#'
flags	Optional modifiers: 'g' = global, 'i' = case-insensitive	'g'

Key Takeaways

Use regexp_replace to replace text matching a regex pattern in PostgreSQL strings.

Include the 'g' flag to replace all occurrences, not just the first.

Escape backslashes in regex patterns with double backslashes in SQL strings.

Flags like 'i' enable case-insensitive matching for flexible replacements.

regexp_replace is powerful for cleaning or formatting text data using patterns.