PostgreSQL SUBSTRING() Function With Examples

SUBSTRING Function in PostgreSQL

The SUBSTRING function in PostgreSQL is a vital function for manipulating textual data. It allows users to retrieve a portion of a string based on a specified starting point and, optionally, a specified length. This function is particularly useful for data preprocessing, text analysis, and data cleansing tasks.

Using the SUBSTRING function, you can extract any part of passed string value according to your needs.

Syntax

The syntax for the PostgreSQL SUBSTRING() function is as follows:

Parameters

The SUBSTRING() function requires two main parameters, with an optional third parameter:

• string: This is the input string from which you want to extract a substring.
• start: Specifies the position within the string where the extraction should begin. The position is a positive integer, with the first character being at position 1.
• length: (Optional) Defines the number of characters to extract from the string. If not provided, the function will extract all characters from the starting position to the end of the string.

Examples

Example 1:

Consider the following SQL query:

In this example, the query extracts a substring from the source string starting at position 14. The expected output is:

Output:

The SUBSTRING function initiates the extraction from the 14th position, which corresponds to the character p in the source string. It then proceeds to extract all subsequent characters until the end of the string, resulting in the output powerful.

Example 2:

Let's examine another example:

Output:

The SUBSTRING function initiates the extraction from position $0$, which is the character 'S' in the source string Scaler is amazing. The length parameter is set to $7$, indicating that the function should extract a substring of $7$ characters.

The result is Scaler, representing the first $7$ characters of the source string. Notably, the character at position $0$ is included in the extracted substring.

Example 3:

Let's consider a case where we want to extract a portion of a URL:

Output:

In this example, the SUBSTRING function starts extracting from position 9 (which is the characer w in the input string https://www.example.com) and continues until the end of the string, providing the result www.example.com.

Example 4:

Now, let's extract a specific segment from a longer text:

Output:

In this case, the function begins at position 9 (i.e. the character 't') and extracts a substring with a length of 4 characters, resulting in 'this'.

Example 5:

Consider a scenario where we need to remove a prefix from a string:

Output:

In this example, the function starts at position 8 (i.e., the character 'T') and extracts the remaining part of the string, yielding 'Text'.