Cloud

SUBSTRING

The SUBSTRING function extracts a part of a string and returns the result. SUBSTR is an alias for SUBSTRING. Both names produce the same result.

Syntax

Use either name (SUBSTRING or SUBSTR) with the same syntax:

2 Arguments

SUBSTRING(string, start_position)

3 Arguments

SUBSTRING(string, start_position, length)

Both syntaxes have input and return of type string.

Start position

Use start_position as the starting position, specifying the part from where the substring is to be returned. It is written as an integer value.

Input Return

start_position < string

The start_position is a given character in the string. The count starts from the first character.

start_position > string

Returns an empty substring.

start_position = negative value

The count starts from the provided negative value, with subsequent characters yielded as it approaches 0.

If the index is less than or equal to 0, no characters are returned. Once it exceeds 0, characters from the string are yielded, starting from the first one.

Length

Use length to determine the number of characters to be extracted. It can be one or more characters.

Input Return

length = 0

Returns an empty substring.

length is not set

The function starts from the specified start_position and ends at the last character of the string.

length = negative value

Returns an error.

Examples

Extract a substring from position 1

SELECT SUBSTRING('RedpandaDocumentation', 1, 8);

This displays the substring from position 1:

substring
-----------
 Redpanda

SUBSTRING with specified start_position and length

In this example, the start_position is set to position 6 and 5 characters are extracted:

SELECT SUBSTRING('Watermelon', 6, 5) AS "Fruit";

The query returns:

Fruit
-------
 melon

SUBSTRING with length = 0

This query extracts a string with length = 0:

SELECT SUBSTRING('Watermelon', 6, 0) AS "Fruit";

This displays an empty output as there is no length specified:

Fruit
-------

SUBSTRING with length = negative value

This example shows what happens when length is specified with a negative value:

SELECT SUBSTRING('Watermelon', 6, -2) AS "Fruit";

Instead of extracting the string from the last characters, it returns an error:

ERROR:  Length of substring cannot be negative

SUBSTRING with start_position > string

The string Watermelon has only ten characters. This example shows what happens when the specified start_position is larger than the string’s characters:

SELECT SUBSTRING('Watermelon', 20, 2) AS "Fruit";

This displays an empty output:

Fruit
-------

SUBSTRING with 2 arguments

In this example, the start_position is set to position 6:

SELECT SUBSTRING('database', 6) AS "Result";

This displays the substring from position 6:

Result
--------
 ase