substr()

A system function for returning a substring from a string

substr() is a system function for returning a substring from the specified position within a string.

substr() was added in PostgreSQL 6.1.

Usage

substr ( string text, start integer [, count integer ] ) → text

substr() returns the portion of the string from the specified start position (with the first character being counted from 1), optionally for the number of specified characters, otherwise until the end of the string. An empty string is returned if the start position exceeds the end of the string. If a negative start value is provided, the entire string is returned; if the number of characters is specified, characters from the start of the string until the position derived from adding count to start will be returned.

Functionally substr() is equivalent to the SQL standard, albeit more verbose substring() function.

Change history

Examples

Basic usage of substr():

postgres=# SELECT substr('foobar', 4);
 substr 
--------
 bar
(1 row)

Return a specified number of characters:

postgres=# SELECT substr('foobar', 3, 3);
 substr 
--------
 oba
(1 row)

substr() works with multibyte characters:

postgres=# SELECT substr('ほげほげ', 1, 2);
 substr 
--------
 ほげ
(1 row)

A negative start value is possible, albeit of limited practical use:

postgres=# SELECT substr('foobar', -3);
 substr 
--------
 foobar
(1 row)

postgres=# SELECT substr('foobar', -3, 3);
 substr 
--------
 
(1 row)

postgres=# SELECT substr('foobar', -3, 7);
 substr 
--------
 foo
(1 row)

A negative substring length is not permitted:

postgres=# SELECT substr('foobar', 4, -1);
ERROR:  negative substring length not allowed

Categories

System function

See also

substring(), strpos()