How to Use the PostgreSQL Substring (Part 2)

Introduction

This is part two in a tutorial series on how to use the PostgreSQL substring function. The substring function in Postgres is utilized for extracting a substring that contains a specified number of characters from a specified position in a particular string. This allows for extracting just part of a string, instead of returning the entire string. The substring function in Postgres has three required parameters. These include: 1) the string or the name of the column; 2) the starting position and; 3) the length or the number of the character. While the third parameter or the length is optional, the string is required as this is what is targeted for extracted. However, if the third parameter isn’t specified, then the system will return the entire string from the starting position. Part two of this tutorial series will provide working examples of how these parameters are used in the PostgreSQL substring function.

Prerequisites

• PostgreSQL must be properly installed on the local computer in order to be able to follow the examples in this tutorial demonstrating the PostgreSQL substring function.

• A working knowledge in PostgreSQL is needed to understand the instructions.

Example of Substring() Function

The following “employee” table is used in this PostgreSQL substring example:

Notice that the substring function of the “first_name” column was used on the above table and only the staring position was specified. Also note that the system will return the entire string from the starting position if the third parameter, meaning the number of characters designated for extraction, isn’t specified.

Following is an example query:

The results of the above query should resemble the following:

It is important to note that if only the string will be extracted from the numbers of characters, then the starting position of “1” must still be specified. This is demonstrated in the following the query:

The results of the query should resemble the following:

Note that a negative substring can also be specified in the starting position and the negative starting position will be deducted from the length of the string. However, a negative substring length is not allowed.

This is shown in the following example where the second parameter is defined as -2 in the “first_name” column and the length is defined as “6.” This will result in the length being counted from -2 position and will return just three characters as a result. Following is the example query:

The result of the above query should resemble the following:

Conclusion

This was part two in a tutorial series on how to use the PostgreSQL substring function. Part two of this series provided an example of substring function and specifically covered how the parameters of PostgreSQL substring function work. Bear in mind the purpose of the substring function is to extract and return just a portion of the string that will always begin with the first character of the string, or the “1” position, unless another position is specified. Remember that while a negative substring can be specified in the starting position, a negative substring length is not allowed. If problems occur while trying to execute the examples here in part two, review the instructions in part one of this series on How to Use the PostgreSQL Substring.