PostgreSQL has an extensive collection of built-in string functions that allow us to manipulate text values. One task we might want to perform is to extract a substring from a larger string. Fortunately, PostgreSQL caters for our needs with at least two functions that make this a breeze.

The SUBSTRING() Function

The substring() function returns a substring from a string, based on various criteria. We can extract a substring based on a specified start point, or based on a regular expression (POSIX or SQL). We can also specify the number of characters to return.

Example of using a start point:

SELECT substring('Payout ratio' from 4 );

Result:

out ratio

Here, I specified that the function should return a substring, starting from the fourth character.

By default it returned everything after that point. We also have the option of specifying the length of the substring to return:

SELECT substring('Payout ratio' from 4 for 3 );

Result:

out

We can also provide a negative start point:

SELECT substring('Payout ratio' from -4 for 8 );

Result:

Pay

In this case, if we specify a substring length (like we did in this example), it is impacted by the negative value.

Here’s an example of of using a basic POSIX regular expression:

SELECT substring('Payout ratio' from 'r..' );

Result:

rat

The SUBSTR() Function

The substr() function does a subset of the actions that substring() does. In particular, the substr() function returns a substring based on the starting index of the substring:

SELECT substr('Corporate action', 11);

Result:

action

And just like with the substring() function, we can specify a substring length:

SELECT substr('Corporate action', 11, 3);

Result:

act

And we can also provide a negative start position:

SELECT substr('Corporate action', -5, 10);

Result:

Corp