How to Use the PostgreSQL Substring Function (Part 1)

Introduction

When you’re performing a PostgreSQL query, you may want to extract just a portion of a given string value. The SUBSTRING() function makes it easy to extract a substring from a larger string value or a column value– all you need to provide is a specified starting position and the length of the desired substring. You can use the SUBSTRING() function on any column that has a string data type such as char, varchar or text. In this first installment of our two-part article series, we’ll take a closer look at the PostgreSQL SUBSTRING function and go over some examples of its use.

Prerequisites

Before you begin this tutorial, make sure you have PostgreSQL installed on your computer so that you can try out the example statements presented in the article. It’s helpful to have some knowledge of PostgreSQL in order to follow along and get the most out of the examples.

PostgreSQL Substring Syntax

Let’s take a look at the syntax of the SUBSTRING() function:

Notice that it can take up to three parameters:

• string – This refers to the name of the string or the name of the column from which the substring will be extracted. Keep in mind that the specified column must have a string data type.
• strt – This represents the starting position of the extracted substring. If this value is set to zero, the substring will start at the first character of the string.
• lngth – This represents the number of characters that will be extracted from the string. If you don’t supply a value for this parameter, all characters from the specified starting position of the substring to the end of the string will be extracted.

Example of Substring() Function

For our first example, we’ll define a string from which we will extract a substring. This string will be ‘postgres’, and the SUBSTRING() function will begin at the third character of the string and have a length of five characters:

The result of this query will look like this:

Example of Substring() Function Using Column

Our next example will make use of a table named employees. If you’d like to set up this sample table and follow along with the example, the contents of the table can be seen below:

When you use the SUBSTRING() function with a column, the column name needs to be enclosed in double quotes, while single quotes are used to enclose strings. In this example, we will extract the first three letters of the employee first_name and set it as nickname. Shown below is the query that we’ll execute:

The result of this query should look like the following:

As you can see, the SUBSTRING() function successfully extracted a three-character “nickname” for each employee in the table.

Conclusion

When you need to pull out a certain segment of a string or column value in PostgreSQL, the SUBSTRING() function makes it easy to get the data you need. In this first article of our two-part series, we provided an overview of the PostgreSQL SUBSTRING function and discussed the parameters used with the function. We also reviewed some examples that demonstrate the function’s use. The second installment in this series will pick up where we left off and continue our discussion of the PostgreSQL SUBSTRING function.