PostgreSQL substr() Function

The PostgreSQL substr() function extracts and returns a substring from a specified string according to the specified starting position and length.

substr() Syntax

This is the syntax of the PostgreSQL substr() function:

substr(string, start)


substr(string, start, length1)

substr(string, start[, length]) is equivalent to substring(string FROM start[ FOR length]).



Required. The string where to get a substring.


Required. The starting position of the substring.


Optional. The length of the substring. The default is to extract to the end of the string.

Return value

The PostgreSQL function substr() extracts a substring of length length starting at position start from the string string and returns it. If length is not specified, substr() will extracts the substring from the index start to the end of string string.

If start + length exceeds the length of string , return the substring from start to the end of the string string.

If any parameter is NULL, the function will return NULL.

substr() Examples

This example shows how to use the substr() function to extract the string starting at position 7 in a string.

SELECT substr('hello world', 7) AS "substr('hello world', 7)";
 substr('hello world', 7)

You can also specify the number of characters to extract (the length of the substring), for example:

SELECT substr('hello world', 7, 5) AS "substr('hello world', 7, 5)";
 substr('hello world', 7, 5)