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:

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:

The result of this query will look like the following:

Here is another example of the Postgres position function using the table named student:

In our next example, we will look for the position of the substring ‘e’ in the column “name”.

The result of this query can be seen below:

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.

The result of this query is shown below:

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.