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
andFROM
. - 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
1 | 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:
1 2 3 4 5 6 | 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:
1 2 3 4 5 6 | 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_tech | t_name | t_category |
---|---|---|
0 | Oracle | DB Relational Old |
1 | Mongo | DB NoSQL Flexible |
2 | PostgreSQL | DB Relational Free |
3 | MySQL | DB Relational Free |
4 | Python | Lang Newish Flexible |
5 | PHP | Lang Old Pervasive |
6 | Java | Lang 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:
1 2 3 4 | 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_tech | t_name | t_category |
---|---|---|
0 | Oracle | DB Relational Old |
1 | Mongo | DB NoSQL Flexible |
2 | PostgreSQL | DB Relational Free |
3 | MySQL | DB Relational Free |
4 | Python | Lang Newish Flexible |
5 | PHP | Lang Old Pervasive |
6 | Java | Lang 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:
1 2 3 4 5 6 | 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_tech | t_name | i_position_of_first_space |
---|---|---|
0 | Oracle | 3 |
1 | Mongo | 3 |
2 | PostgreSQL | 3 |
3 | MySQL | 3 |
4 | Python | 5 |
5 | PHP | 5 |
6 | Java | 5 |
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_tech | t_name | t_category |
---|---|---|
0 | Oracle | DB Relational Old |
1 | Mongo | DB NoSQL Flexible |
2 | PostgreSQL | DB Relational Free |
3 | MySQL | DB Relational Free |
4 | Python | Lang Newish Flexible |
5 | PHP | Lang Old Pervasive |
6 | Java | Lang Pervasive |
1 2 3 4 5 6 7 | 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