Summary: in this tutorial, we will introduce you to PostgreSQL substring function that extracts a substring from a string.
Introduction to PostgreSQL substring function
The substring function returns a part of string. The following illustrates the syntax of the substring
function:
SUBSTRING ( string ,start_position , length )
Code language: SQL (Structured Query Language) (sql)
Let’s examine each parameter in detail:
- string is a string whose data type is char, varchar, text, etc.
- start_position is an integer that specifies where you want to extract the substring. If
start_position
equals zero, the substring starts at the first character of the string. Thestart_position
can be only positive. Though in other database systems such as MySQL the substring function can accept a negativestart_position
. - length is a positive integer that determines the number of characters that you want to extract from the string beginning at
start_position
. If the sum ofstart_position
andlength
is greater than the number of characters in thestring
, the substring function returns the whole string beginning atstart_position
. The length parameter is optional. If you omit the length parameter, the substring function returns the whole string started atstart_position
.
PostgreSQL substring examples
See the following examples:
SELECT
SUBSTRING ('PostgreSQL', 1, 8); -- PostgreS
SELECT
SUBSTRING ('PostgreSQL', 8); -- SQL
Code language: SQL (Structured Query Language) (sql)
In the first statement, we extract a substring that has length of 8 and it is started at the first character of the PostgreSQL
string. we get PostgreS
as the result. See the following picture:

In the second statement, we extract a substring started at position 8 and we omit the length parameter. The substring is a string beginning at 8, which is SQL
.

PostgreSQL provides another syntax of the substring function as follows:
substring(string from start_position for length);
Code language: SQL (Structured Query Language) (sql)
In this form, PostgreSQL puts three parameters into one. See the following example:
SELECT
SUBSTRING ('PostgreSQL' FROM 1 FOR 8); -- PostgreS
SELECT
SUBSTRING ('PostgreSQL' FROM 8); -- SQL
Code language: SQL (Structured Query Language) (sql)
The results are the same as the one in the first example.
In the following example, we query data from the customer table. We select last_name
and first_name
column. We get the initial name by extracting the first character of the first_name
column.
SELECT
last_name,
SUBSTRING( first_name, 1, 1 ) AS initial
FROM
customer
ORDER BY
last_name;
Code language: SQL (Structured Query Language) (sql)

Extracting substring matching POSIX regular expression
In addition to the SQL-standard substring function, PostgreSQL allows you to use extract a substring that matches a POSIX regular expression. The following illustrates the syntax of the substring function with POSIX regular expression:
SUBSTRING(string FROM pattern)
Code language: SQL (Structured Query Language) (sql)
Or you can use the following syntax:
SUBSTRING(string,pattern);
Code language: SQL (Structured Query Language) (sql)
Note that if no match found, the substring function return a null value. If the pattern contains any parentheses, the substring function returns the text that matches the first parenthesized subexpression.
The following example extracts the house number (maximum 4 digits, from 0 to 9) from a string:
SELECT
SUBSTRING (
'The house no. is 9001',
'([0-9]{1,4})'
) as house_no
Code language: SQL (Structured Query Language) (sql)

Extracting substring matching a SQL regular expression
Besides POSIX regular expression pattern, you can use SQL regular expression pattern to extract a substring from a string using the following syntax:
SUBSTRING(string FROM pattern FOR escape-character)
Code language: SQL (Structured Query Language) (sql)
This form of substring
function accepts three parameters:
string
: is a string that you want to extract the substring.- escape-character: the escape character.
pattern
is a regular expression wrapped inside escape characters followed by a double quote ("). For example, if the character#
is the escape character, the pattern will be#"pattern#"
. In addition, thepattern
must match the entirestring
, otherwise, the substring function will fail and return aNULL
value.
See the following examples:
SELECT SUBSTRING (
'PostgreSQL'
FROM
'%#"S_L#"%' FOR '#'
); -- SQL
SELECT SUBSTRING (
'foobar'
FROM
'#"S_Q#"%' FOR '#'
); -- NULLL
Code language: SQL (Structured Query Language) (sql)
PostgreSQL provides another function named substr
that has the same functionality as the substring
function.
Summary
- Use the PostgreSQL substring functions to extract substring from a string.