Skip to main content
Documents
Basic SyntaxMath FunctionsDate FunctionsJSON FunctionsDatabasesTables & Schema ManagementString FunctionsTriggersIndexes

PostgreSQL REGEXP_REPLACE

```html

The `REGEXP_REPLACE` function in PostgreSQL is used to search a string for a regular expression pattern and replace occurrences of the pattern with a specified replacement string. It is a powerful tool for string manipulation and text data cleaning.

Usage

The `REGEXP_REPLACE` function is typically used to modify strings by replacing specific patterns, which is especially useful in data transformation tasks. It allows for complex pattern matching using regular expressions and operates on text and character varying data types.

sql
REGEXP_REPLACE(source, pattern, replacement [, flags])
  • source: The string to be searched.
  • pattern: The regular expression pattern to search for. Note that backslashes in patterns (e.g., for backreferences) may need to be doubled due to escape sequences in SQL strings.
  • replacement: The string to replace matched patterns. The replacement string can include backreferences (e.g., `\1` for the first captured group) to use parts of the matched pattern in the replacement.
  • flags: Optional flags to control the behavior of the matching process.

Examples

1. Basic Replacement

sql
SELECT REGEXP_REPLACE('apple pie', 'apple', 'banana');

This query replaces the word "apple" with "banana" in the string, resulting in "banana pie".

2. Case-Insensitive Replacement

sql
SELECT REGEXP_REPLACE('Hello World', 'world', 'there', 'i');

Using the 'i' flag for case insensitivity, this replaces "World" with "there", resulting in "Hello there".

3. Replace with Backreferences

sql
SELECT REGEXP_REPLACE('123-456-7890', '(\\d{3})-(\\d{3})-(\\d{4})', '(\\1) \\2-\\3');

This example formats a phone number by capturing groups and reorganizing them, producing "(123) 456-7890".

Tips and Best Practices

  • Understand regular expressions. Familiarize yourself with regular expression syntax to effectively use `REGEXP_REPLACE`.
  • Use flags wisely. Utilize flags like 'i' for case-insensitive matching or 'g' for global replacement to meet specific needs.
  • Test expressions. Before applying complex replacements, test your regular expressions to ensure they work as intended.
  • Beware of performance. Regular expression operations can be computationally expensive; use them judiciously on large datasets.
  • Escape characters correctly. Ensure special characters in regular expressions or SQL strings are properly escaped to avoid errors.
```