PostgreSQL 18 reference · String Functions

PostgreSQL SUBSTRING() Function

Extract part of a string by 1-based position and length, or the part matching a regular expression.

PostgreSQL 18

Create a free account to save this page, take notes and track your progress. Sign in

Syntax

substring(string FROM start FOR count)  |  substring(string, start, count)  |  substring(string FROM pattern)

Parameter	Description
`string`	The input text.
`start`	The first character, counting from 1.
`count` optional	How many characters.
`pattern` optional	A POSIX regular expression; the first parenthesized group (or the whole match) is returned.

Text, or NULL when a pattern doesn't match.

By position

SELECT substring('ReadyCamel' FROM 1 FOR 5) AS first5,
       substring('ReadyCamel', 6) AS rest;

Output

 first5 | rest
--------+-------
 Ready  | Camel
(1 row)

Run this on your computer — see “Get Started” for setup.

By regular expression

SELECT substring('Order #A-1042 shipped' FROM '#([A-Z]-\d+)') AS order_ref;

Output

 order_ref
-----------
 A-1042
(1 row)

Run this on your computer — see “Get Started” for setup.

LEFT() · Return the first n characters of a string. A negative n returns all but the last |n| characters.
RIGHT() · Return the last n characters of a string. A negative n returns all but the first |n| characters.
POSITION() · Return the 1-based position of a substring inside a string, or 0 if it isn't found. strpos() takes the arguments in the other order.
SPLIT_PART() · Split a string on a delimiter and return the nth field (1-based). Negative n counts from the end.
SQL String Functions · tutorial