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

i_string_position = STRPOS(t_string_original, s_string_to_find);

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:

DECLARE
t_string_original := 'Python is a versatile coding language.';
BEGIN
  SELECT
  i_string_position := STRPOS(t_string_original, "is");
END

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:

DECLARE
t_string_original := 'Python is a versatile coding language.';
BEGIN
  SELECT
  i_string_position := STRPOS(t_string_original, " ");
END

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:

id_techt_namet_category
0OracleDB Relational Old
1MongoDB NoSQL Flexible
2PostgreSQLDB Relational Free
3MySQLDB Relational Free
4PythonLang Newish Flexible
5PHPLang Old Pervasive
6JavaLang Pervasive

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:

SELECT
  STRPOS(t_category, " ") AS i_position_of_first_space
FROM
  technologies;

The above query will return the following results:

i_position_of_first_space
3
3
3
3
5
5
5

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”:

id_techt_namet_category
0OracleDB Relational Old
1MongoDB NoSQL Flexible
2PostgreSQLDB Relational Free
3MySQLDB Relational Free
4PythonLang Newish Flexible
5PHPLang Old Pervasive
6JavaLang Pervasive

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:

SELECT
  id_tech
  , t_name
  , STRPOS(t_category, " ") AS i_position_of_first_space
 FROM
  technologies;

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:

id_techt_namei_position_of_first_space
0Oracle3
1Mongo3
2PostgreSQL3
3MySQL3
4Python5
5PHP5
6Java5

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):

id_techt_namet_category
0OracleDB Relational Old
1MongoDB NoSQL Flexible
2PostgreSQLDB Relational Free
3MySQLDB Relational Free
4PythonLang Newish Flexible
5PHPLang Old Pervasive
6JavaLang Pervasive
SELECT
  id_tech
  , t_name
  , STRPOS(t_category, " ") AS i_position_of_first_space
  , SUBSTRING(t_category, 1, i_position_of_first_space - 1)
 FROM
  technologies;

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().

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.