Postgres Strpos Function and Nested Strings in SQL

Introduction

In this tutorial, we will show how to use the Postgres Strpos function with nested strings in SQL to isolate words in multi-word phrases, including:

• What? What does the PostgreSQL STRPOS function do?
• How? How do we use this function in our PostgreSQL SQL commands?
• Why? When do we need these functions? We’ve included a real world use case for your learning pleasure.

Prerequisites

• Beginner-level understanding of writing SQL for PostgreSQL (or MS SQL Server, Oracle, MySQL, etc.) using the free PG Admin tool or some other relational db admin tool, or by via code or script (compiled or not, object-oriented or not) languages like Python, PHP, Java, C#, ASP.Net, Javascript, VB.Net, Ruby, Node, etc.) that provides a database connection, as well as a method for sending it PL/SQL queries, to get data or make changes to your database.
• Understanding of the use of the most basic SQL (PL/SQL) statements, like SELECT and FROM.
• Knowledge of what a string is and what a function is.

What is the STRPOS() function?

The PostgreSQL STRPOS function returns the position of a nested substring. So if you provide the function with something like, “Postgres is the best relational database system,” and tell it you want to know the position in that string of a nested string, the STRPOS() function will return “8” as an integer.

How do we use the STRPOS function?

Syntax

So, if we want to copy a chunk out of one string into another, we feed the STRPOS function two pieces of information:

• The original string, which we call “t_string_original,” above.
• The string we want to find.

Now we take a small step by filling our syntax with a bit of data:

The example above returns “8” as an integer because “is” begins at character 8 within the original string of “Python is a versatile coding language.”.

If we make a slight tweak to the above code, we can see an example of a Substr() search that most coders will use at some point; searching for the first instance of a space within a string, so that we can delineate a word or words:

In the example above, our STRPOS() function returns the integer, “7”.

Let’s look at a slightly deeper example in SQL of how STRPOS works. Here, we want to search our db table for the first word in the value stored in a field (column) that may have multiple words.

Our test table, named technologies, with pre-filled-in data:

If our goal to return the first word in the t_category field (column) of our table, we could use the LIKE and “%” operators but we want to learn STRPOS. Also, we want to find the position of that first word and we can’t assume we know how long or what that word is, so the LIKE-and-“%” method won’t serve us in this use case. Here’s the code:

The above query will return the following results:

Now while that example above may help you understand better a way we can use the STRPOS function, the information returned above is most likely useless because we are not returning any identifying columns, so that we can know which technology t_name goes with which i_position_of_first_space. So let’s “get real”.

Why use the STRPOS function?

Use case of organizing coding and database technologies

Again, here is our full table. We’ll name it “technologies”:

The idea we mentioned above about our first example that returns only one column of data that may be useless to us without at least an id or name of what tech is being referred to. So we’ll now modify that query:

Note: If you care about normalization, you might look at the above table and think to yourself, “I would normalize the table by creating a new one called ‘categories’ and connect it to this table with a new column in this table called id_category.” But that is a topic for another lesson.

The new query that gives us the information we need:

Here are the recordset of results Postgres returns to us, giving us id_tech, t_name, and i_position_of_first_space for each row:

Okay, so now we know where the first space is. That means we know the first word is defined by position 1 through position (i_position_of_first_space – 1), right?

Let’s walk through the logic of how to do this and take it one step further:

• (1) Use the STRPOS() function to get i_position_of_first_space.
• (2) Subtract 1 from that number, so we have the exact length of that first word.
• (3) Use the SUBSTRING() function to isolate the word.

Here’s the SQL to accomplish that task (and first we’ll list the full contents of our original table named technologies, for your ease):

Notes:

• In many cases, like if you are using arrays, we begin counting at zero. So it is important to keep in mind that STRPOS() starts counting the position at “1”.
• Same functionality but different syntax to the Position() function.
• Look for our other articles that utilize the simple, but powerful and useful STRPOS Postgres function.

Conclusion

In this tutorial article we learned the use of the STRPOS() function in PostgreSQL. In part of this tutorial, we used a real world example. Finally, we touched on another powerful string related function called SUBSTRING().