A Brief Overview of SUBSTR() in PostgreSQL

In PostgreSQL, we can use the the substr() function to return a substring from a string, based on a starting point. We have the option of specifying how many characters to return.

We pass the string as the first argument and the start position as the second. If we want to specify how long the substring should be, we can pass a third argument that specifies how many characters to return.

The function returns the same result as the substring() function (which uses a slightly different syntax).

Example

Here’s an example to demonstrate:

SELECT substr('PostgreSQL', 8);

Result:

SQL

In this example, the extracted substring consisted of everything from position 8 on.

Here’s another example:

SELECT substr('Risk adjusted returns', 6);

Result:

adjusted returns

Specifying a Length

As mentioned, we can specify how long the substring should be. We do this by adding a third argument that specifies how many characters to return:

SELECT substr('Risk adjusted returns', 6, 8);

Result:

adjusted

Out of Range Starting Position

If the original string is shorter than the starting position, then an empty string is returned:

SELECT substr('Risk adjusted returns', 25);

Result:

 substr 
--------
 
(1 row)

Negative Starting Position

Passing a negative value as the starting position results in the starting position being moved back. However, because the default behaviour is to return the remainder of the string, the effect is that the whole string is returned (unless we specify a substring length).

Here’s an example of what I mean:

SELECT substr('Risk adjusted returns', -5);

Result:

Risk adjusted returns

Here, I specified a starting position of -5, which put the starting point back before the string even began.

First observation is that it doesn’t pad the start of the string by that amount. It simply starts wherever the string starts.

Second observation is that it returned the whole string. That’s because, if we don’t provide a third argument, the function returns all remaining characters.

Let’s provide a third argument:

SELECT substr('Risk adjusted returns', -5, 10);

Result:

Risk

This time it returned a substring that matched our numbers. Interestingly, it counted ten characters from the starting point, even though a lot of those characters didn’t actually exist. In this case it returns only the characters that exist. But this shows that we can still specify a negative starting point and specify the substring’s length based on that start point.

Null Arguments

If any of the arguments is null, then null is returned:

SELECT 
    substr(null, -5, 10) AS "1",
    substr('Risk adjusted returns', null, 10) AS "2",
    substr('Risk adjusted returns', -5, null) AS "3",
    substr(null, null, null) AS "4";

Result:

  1   |  2   |  3   |  4   
------+------+------+------
 null | null | null | null

Wrong Argument Type

Both arguments need to be text. Passing the wrong type results in an error:

SELECT substr(1234567, 6, 8);

Result:

ERROR:  function substr(integer, integer, integer) does not exist
LINE 1: SELECT substr(1234567, 6, 8);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

And:

SELECT substr('Risk adjusted returns', 'Six', 8);

Result:

ERROR:  invalid input syntax for type integer: "Six"
LINE 1: SELECT substr('Risk adjusted returns', 'Six', 8);
                                               ^

And:

SELECT substr('Risk adjusted returns', 6, 'Eight');

Result:

ERROR:  invalid input syntax for type integer: "Eight"
LINE 1: SELECT substr('Risk adjusted returns', 6, 'Eight');
                                                  ^

That said, the first argument can be the text representation of numbers:

SELECT substr('1234567', 3, 3);

Result: