Regular expression functions (regexp_match, regexp_replace) in PostgreSQL

Introduction

Regular expression functions help find or change text patterns inside data. They make searching and editing text easier and faster.

You want to find if a phone number matches a pattern like [phone].

You need to extract a part of an email address from a list of emails.

You want to replace all spaces in a text with underscores.

You want to clean up data by removing unwanted characters from strings.

Syntax

PostgreSQL

regexp_match(string, pattern [, flags])
regexp_replace(string, pattern, replacement [, flags])

regexp_match returns the first part of the string that matches the pattern as an array.

regexp_replace replaces parts of the string that match the pattern with the replacement text.

Examples

Finds the first group of digits in the string 'hello123'.

PostgreSQL

SELECT regexp_match('hello123', '\\d+');

Replaces spaces with underscores in the string 'hello world'.

PostgreSQL

SELECT regexp_replace('hello world', ' ', '_');

Extracts groups of letters and digits from the string.

PostgreSQL

SELECT regexp_match('abc123def', '([a-z]+)(\\d+)([a-z]+)');

Replaces digits in the string with '456'.

PostgreSQL

SELECT regexp_replace('abc123def', '\\d+', '456');

Sample Program

This example finds a phone number pattern in the text and then replaces it with a hidden version.

PostgreSQL

SELECT regexp_match('My phone is 555-1234', '\\d{3}-\\d{4}') AS phone_match;

SELECT regexp_replace('My phone is 555-1234', '\\d{3}-\\d{4}', 'XXX-XXXX') AS phone_hidden;

Important Notes

Remember to double backslashes (\\) in patterns because PostgreSQL uses backslash as an escape character.

Flags like 'i' can be added to make matching case-insensitive.

regexp_match returns NULL if no match is found.

Summary

Use regexp_match to find text patterns and get matching parts.

Use regexp_replace to change parts of text that match a pattern.

Regular expressions let you work with complex text rules easily.