PostgreSQL provides a built-in function named SUBSTRING() that extracts a substring from any specific string. The SUBSTRING() function accepts three parameters: a string, starting position, and length. The starting position” and “length” parameters are optional that can be skipped depending on the situation.

This write-up will present a detailed overview of extracting a substring from a string using the PostgreSQL SUBSTRING() function. So, let's get started.

How to Use SUBSTRING() Function in PostgreSQL?

The following snippet illustrates the syntax of the Postgres SUBSTRING() function:

SUBSTRING(str [from <start_ind>] [for <len>]);

Here,

- str represents a string.

- start_ind represents a starting position(from where the string extraction will start).

- len represents the length of the sub-string(sequence of characters) to be extracted.

Example #1: How Does SUBSTRING() Function Work in Postgres?

Suppose we have to get the six characters from a string “commandprompt”. The sub-string should be extracted from the 8th index:

SELECT SUBSTRING('commandprompt' from 8 for 6);

The above query will return a substring starting from the 8th index, and it consists of 6 characters:

The output proves that the SUBSTRING() function extracted the desired substring from the given string.

Example #2: Use SUBSTRING() Function Without Length Parameter?
Omitting the length parameter will extract a substring from the specified starting position to the last position:

SELECT SUBSTRING('commandprompt', 8);

This time, we didn’t specify the length parameter, so the SUBSTRING() function extracted the desired substring from the specified index to the last index of the string.

Example #3: How to Use the SUBSTRING() Function on Table’s Data?

We have a table named bike_details that contains the following content:

SELECT * FROM bike_details;

Suppose we want to display the bike_model and the first three characters of bike_color. To do so, we will SUBSTRING() Function as follows:

SELECT bike_model,
SUBSTRING(bike_color,1,3) as bike_color
FROM bike_details;

In this way, you can extract a substring from a table’s data using the SUBSTRING() function.

Example #4: How to Use ORDER BY Clause With the SUBSTRING() Function?

In this example, we will show you how to use the SUBSTRING() function with the ORDER BY clause:

SELECT bike_model,
SUBSTRING(bike_color,1,3) as bike_color
FROM bike_details
ORDER BY bike_color;

This is how the SUBSTRING() function works in PostgreSQL.

Conclusion

PostgreSQL provides a built-in function named SUBSTRING() that extracts a substring from any specific string. The SUBSTRING() function accepts three parameters: a string, starting position, and length. The “starting position” and length parameters are optional. The starting position determines where the string extraction will start, while the length parameter describes the number of characters to be extracted. This post taught us how the working of SUBSTRING() function with practical examples.