How to Use the PostgreSQL Substring Function (Part 1)
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
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.
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
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:
SELECT SUBSTRING('postgres' FROM 3 FOR 5);
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:
id | first_name | last_name | age | department_name
1 | Lacie | Serrano | 31 | Accounting
2 | Marius | Bryne | 27 | Engineering
3 | Anita | Matthams | 28 | Engineering
4 | Maryam | Cohen | 36 | Marketing
5 | Ronald | Mcmillan | 32 | IT
6 | Payton | Glover | 35 | IT
7 | Ella May | Stevens | 31 | Accounting
8 | Ruari | Ball | 26 | Marketing
9 | Ayyan | Bourne | 30 | Accounting
10 | Manha | Ferry | 28 | Engineering
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:
SUBSTRING("first_name", 1, 3)
The result of this query should look like the following:
first_name | nickname
Lacie | Lac
Marius | Mar
Anita | Ani
Maryam | Mar
Ronald | Ron
Payton | Pay
Ella May | Ell
Ruari | Rua
Ayyan | Ayy
Manha | Man
As you can see, the
SUBSTRING() function successfully extracted a three-character “nickname” for each employee in the table.
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started