SUBSTRING, SUBSTR

Returns a substring starting at the character indicated by the <start> index and including the number of characters defined by <length>. Character indexing starts at index 1.

Syntax

SUBSTRING(<expression> [FROM <start>] [FOR <length>])
SUBSTRING(<expression>, <start> [, <length>])
SUBSTR(<expression>, <start> [, <length>])

Parameters

Parameter	Description	Supported input types
`<expression>`	The input string.	`TEXT`,`BYTEA`
`<start>`	Optional. The starting position for the substring. 1 is the first character. If value < 1 then it start from negative position (all negative positions treated as empty).	`BIGINT`
`<length>`	Optional. The number of characters to be returned by the `SUBSTRING` function. Must be positive or 0. If not specified, `count` by default returns all of the string not specified by the `start` parameter.	`BIGINT`

At least one of the optional arguments <start> and <length> must be given. Negative <start> values begin the substring before the input <expression>. Positions before the input <expression> are considered empty.

Return Type

TEXT or BYTEA. The type of <expression>

Examples

In the example below, the string is offset by 1 and so the SUBSTRING command begins at the first letter, “h”. The <length> of 5 indicates the resulting string should be only five characters long.

SELECT SUBSTRING('hello world' FROM 1 FOR 5);

substring text
hello

Rows: 1Execution time: 4.82ms

In this next example <length> is not provided. This means all characters are included after the <start> index, which is 7.

SELECT SUBSTRING('hello world' FROM 7);

substring text
world

Rows: 1Execution time: 5.26ms

For negative <start> values, empty positions before the input string are selected. Thus, without <length> negative values will select the entire input <string>.

SELECT SUBSTRING('hello world' FROM -2);

substring text
hello world

Rows: 1Execution time: 5.16ms

With a negative <start> value and <length>, the empty positions before the string are counted towards the substring length. In this example, beginning the substring at -2 for 8 characters includes 3 empty positions and 5 positions with characters from the input <expression>.

SELECT SUBSTRING('hello world' FROM -2 FOR 8);

substring text
hello

Rows: 1Execution time: 5.19ms

Overview

Performance and Observability

Security

Self-Managed

Managed service

Guides

SQL reference

Release notes

API reference

Legal

SUBSTRING, SUBSTR