PostgreSQL substring() Function

The PostgreSQL substring() function extracts a substring from a specified string according to a specified starting position and length, or extract a substring according to regular expressions.

`substring()` Syntax

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

substring(string [FROM start] [FOR length])

substring(string FROM pattern)

substring(string SIMILAR pattern ESCAPE escape)

Parameters

string: Required. The string where to get a substring.
start: Optional. The starting position of the substring. The default is 1, that is the beginning of the string.
length: Optional. The length of the substring. The default is to extract to the end of the string.
pattern: Required. Regular expression. FROM pattern uses POSIX regular expressions, and SIMILAR pattern uses SQL regular expression.
escape: Required. The escape character.

Return value

The PostgreSQL function substring() extracts a substring of length length starting at position start from the string string and returns it. If length is not specified, substring() 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.

`substring()` Examples

Syntax 1 Example

SELECT
    substring('hello' FROM 2) AS "substring('hello' FROM 2)",
    substring('hello' FROM 2 FOR 2) AS "substring('hello' FROM 2 FOR 2)",
    substring('hello' FOR 2) AS "substring('hello' FOR 2)";

-[ RECORD 1 ]-------------------+-----
substring('hello' FROM 2)       | ello
substring('hello' FROM 2 FOR 2) | el
substring('hello' FOR 2)        | he

Syntax 2 Example

SELECT
    substring('hello' FROM '^.{2}') AS "substring('hello' FROM '^.{2}')",
    substring('hello' FROM '.{3}$') AS "substring('hello' FROM '.{3}$')";

-[ RECORD 1 ]-------------------+----
substring('hello' FROM '^.{2}') | he
substring('hello' FROM '.{3}$') | llo

Here, POSIX regular expression is used in the FROM pattern clause.

Syntax 3 Example

SELECT substring('hello' SIMILAR '%#"__l#"%' ESCAPE '#');

 substring
-----------
 hel

Here, SQL regular expression is used in the SIMILAR pattern, like LIKE clause.

Note that the SIMILAR statement will only succeed if the SQL regular expression matches the entire string, so the above example uses #" to delimit the part to return.

substring() Syntax