How to Use the Postgres Position Function
Introduction
When you’re working with string data in PostgreSQL, you may need to find a substring that exists within another string. For example, imagine you had a string with the value “I love PostgreSQL”, and you wanted to get the location of the substring “PostgreSQL” within that string. It’s easy to accomplish this task with the help of the position
function. In this article, we’ll show you how to use the Postgres position function to obtain the location of a substring in a specified string.
Prerequisites
Before we proceed with this tutorial, let’s review a couple of prerequisites that are essential for this task. First, you’ll need to have PostgreSQL installed on your computer, since we’ll be executing some queries that make use of the position
function. You’ll also need to have some knowledge of PostgreSQL in order to follow along with the instructions in this article and get the most out of it.
PostgreSQL position function syntax
As we mentioned earlier, the PostgreSQL position
function is used to locate the substring that is specified and returns the position of the string. Here’s the basic syntax for this function:
1 | SELECT POSITION([SUBSTRING] IN [string]); |
In this syntax, ‘substring’ is the string that we’ll be searching for within the main string, and ‘string’ is the main string that will be searched.
Examples of PostgreSQL position function queries
Now that we understand how the function works, let’s look at some examples of its use. In our first example, we have a main string with a value of string
. We will search for the location of the substring i
. We can see how to accomplish this in the query shown below:
1 | SELECT POSITION('i' IN 'string'); |
The result of this query will look like the following:
1 2 3 | POSITION ---------- 4 |
Here is another example of the Postgres position function using the table named student
:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | id | name | grade -----+------------------+------- 32 | Allen James | 89 371 | Jerry Lawrel | 92 536 | Tommy Jones | 85 312 | Michelle Rogers | 85 162 | Kelly Ripa | 87 51 | Fred Smith | 89 322 | Alfred Roosevelt | 89 481 | Wayne Rosing | 91 261 | Samuel Davis | 92 621 | Philip Wilson | 95 782 | Jay Leno | 93 361 | MAX Heindel | 97 891 | Gerry Starr | 94 |
In our next example, we will look for the position of the substring ‘e’ in the column “name”.
1 | SELECT id, name, POSITION('e' IN "name") FROM student; |
The result of this query can be seen below:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | id | name | POSITION -----+------------------+---------- 32 | Allen James | 4 371 | Jerry Lawrel | 2 536 | Tommy Jones | 10 312 | Michelle Rogers | 5 162 | Kelly Ripa | 2 51 | Fred Smith | 3 322 | Alfred Roosevelt | 5 481 | Wayne Rosing | 5 261 | Samuel Davis | 5 621 | Philip Wilson | 0 782 | Jay Leno | 6 361 | MAX Heindel | 6 891 | Gerry Starr | 2 |
Notice that the position returned is the first occurrence of the substring that is found within the string. For example, let’s look the position of the substring r
in the column name
. In the student
table, there is a student named ‘Jerry Lawrel’. This name has three occurrences of the substring r
in it. Since the first substring can be found in the third position, the position
function will return 3
.
1 2 3 | SELECT id, name, POSITION('r' IN "name") FROM student WHERE POSITION('r' IN "name") != 0; |
The result of this query is shown below:
1 2 3 4 5 6 7 | id | name | POSITION -----+------------------+---------- 371 | Jerry Lawrel | 3 312 | Michelle Rogers | 14 51 | Fred Smith | 2 322 | Alfred Roosevelt | 4 891 | Gerry Starr | 3 |
Conclusion
When you need to get the location of a certain substring within another string, the position
function provides a simple way to accomplish the task. This function will return the location of the first occurrence of a given substring within a main string. In this article, we provided several examples of how to use the Postgres position function. With these examples to use as a guide, you’ll be able to utilize this function in your own PostgreSQL queries.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started