PostgreSQL: substring Function

In this PostgreSQL post explains how to use the PostgreSQL substring function with syntax and examples.

Description

The PostgreSQL substring function allows you to extract a substring from a string.

Syntax

The syntax for the substring function in PostgreSQL is:

substring( string [from start_position] [for length] )

Parameters or Arguments

string

The source string.

start_position

Optional. It is the starting position for extraction. If this parameter is omitted, the substring function will start at position 1 (which is the first position in the string).

length

Optional. It is the number of characters to extract. If this parameter is omitted, the substring function will return the entire string (from the start_position to the end of the string).

Note

• The first position in string is 1.

Applies To

The substring function can be used in the following versions of PostgreSQL:

• PostgreSQL 9.4, PostgreSQL 9.3, PostgreSQL 9.2, PostgreSQL 9.1, PostgreSQL 9.0, PostgreSQL 8.4

Example

Let's look at some PostgreSQL substring function examples and explore how to use the substring function in PostgreSQL.

For example:

postgres=# SELECT substring('AODBA.com' for 5);
 substring
-----------

AODBA
(1 row)

postgres=# SELECT substring('AODBA.com' from 1 for 5);
 substring
-----------

AODBA
(1 row)

postgres=# SELECT substring('AODBA.com' from 2 for 5);
 substring
-----------

ODBA.
(1 row)

postgres=# SELECT substring('AODBA.com' from 5 for 7);
 substring 
-----------

A.com
(1 row)

postgres=# SELECT substring('AODBA.com' from 7 for 3);
 substring
-----------

 com
(1 row)

postgres=# SELECT substring('AODBA.com' from 6);
 substring
-----------

 .com
(1 row)