How to Use the Postgres Position Function

Have a Database Problem? Speak with an Expert for Free
Get Started >>

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

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.