Strings

REGEXP_SUBSTR

Returns the first fragment of a string that matches a regular expression — as plain text.

Syntax

REGEXP_SUBSTR(<text>, '<pattern>')
REGEXP_SUBSTR(<text>, '<pattern>', <start>, <match number>)
REGEXP_SUBSTR(<text>, '<pattern>', <start>, <match number>, '<flags>')

The simpler sibling of REGEXP_MATCHES: it returns text rather than an array, and if there's no match — NULL, so the table row doesn't disappear from the result. Extra arguments set the position to search from, which match to take and flags such as 'i'. Added in PostgreSQL 15.

Data for the examples

employees

name	department	salary	bonus	phone
Emma	backend	1200	300	+44 7700 900123
Liam	backend	1000	NULL	NULL
Olivia	frontend	1350	150	+44 7700 900456
Noah	frontend	950	NULL	+44 7700 900789
Sophia	design	1200	200	NULL

These tables already exist in the sandbox — the examples below use them. Want to try this in your own database? Copy the SQL.

The first number

SELECT REGEXP_SUBSTR('Order 1234, box 56', '[0-9]+') AS first_number;

first_number
1234

The second match

SELECT REGEXP_SUBSTR('Order 1234, box 56', '[0-9]+', 1, 2) AS second_number;

second_number
56

1 — search from the first character, 2 — take the second match.

The last six digits of a phone number

SELECT name, phone,

REGEXP_SUBSTR(phone, '[0-9]{6}$') AS last_six

FROM employees;

name	phone	last_six
Emma	+44 7700 900123	900123
Liam	NULL	NULL
Olivia	+44 7700 900456	900456
Noah	+44 7700 900789	900789
Sophia	NULL	NULL

Where there's no phone number, the result is NULL, and the row stays.

Watch out

It takes the first match, not the longest: REGEXP_SUBSTR('a1b22', '[0-9]+') gives 1, not 22.

Case matters. To ignore it, pass the 'i' flag as the fifth argument: REGEXP_SUBSTR('PostgreSQL', 'sql', 1, 1, 'i') gives SQL.

It exists only since PostgreSQL 15. In older versions, use SUBSTRING(<text> FROM '<pattern>') or (REGEXP_MATCHES(<text>, '<pattern>'))[1].

Related functions

REGEXP_MATCHES SUBSTRING REGEXP_LIKE