Regular expression matching (~ operator) in PostgreSQL

Introduction

Regular expression matching helps find text patterns inside data, like searching for words or formats in a list.

You want to find all emails in a list that have a specific domain.

You need to check if phone numbers follow a certain pattern.

You want to filter names that start with a certain letter or letters.

You want to find rows where a text column contains digits.

You want to validate if a string matches a pattern before processing it.

Syntax

PostgreSQL

expression ~ pattern

The ~ operator checks if the expression matches the regular expression pattern.

It returns true if there is a match, false otherwise.

Examples

Checks if 'hello123' contains any digit.

PostgreSQL

SELECT 'hello123' ~ '[0-9]';

Checks if 'abc' starts with the letter 'a'.

PostgreSQL

SELECT 'abc' ~ '^a';

Checks if the string ends with '@example.com'.

PostgreSQL

SELECT '[e-mail]' ~ '@example\.com$';

Sample Program

This creates a temporary table with some names. Then it selects only the names that contain any digit.

PostgreSQL

CREATE TEMP TABLE users(name TEXT);
INSERT INTO users VALUES
('Alice'),
('Bob123'),
('Charlie_99'),
('David');

SELECT name FROM users WHERE name ~ '[0-9]';

Important Notes

Regular expressions are case sensitive by default. Use ~* for case-insensitive matching.

Escape special characters in patterns with double backslash (\\) if needed.

Use ^ and $ to match the start and end of the string respectively.

Summary

The ~ operator checks if a string matches a regular expression pattern.

It returns true or false, helping filter data based on text patterns.

Use it to find or validate text formats inside your database.