Postgres Search a Column

Introduction

We will teach you how to use various applicable functions in Postgres to search a column for a specific character or string (word). The functions we will explore here include: Substring, Position, StrPos, and Reverse.

Prerequisites

  • Ability to- and knowledge of- how to write Postgres SQL.
  • Knowledge of the use of the most basic SQL (PL/SQL) statements, including SELECT and FROM.
  • Understanding of what a string is and what concatenation means.

Before learning how to use Position, Substring, and StrPos, we’ll explore:

Postgres REVERSE function

A quickie of the syntax of the REVERSE function in Postgres:

t_source_reversed := REVERSE(t_source);

If the value in t_source is “lanoitaleR esabataD” then the value of t_source_reversed becomes “Relational Database”.

Postgres 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.

Strpos syntax

i_position_of_chars := STRPOS(t_chars_original, t_chars_to_find);

To copy a chunk out of one string into another, we give STRPOS two parameters:

  • The original set of characters, which we call “t_chars_original” above.
  • The characters we are looking for; “t_chars_to_find”.

Postgres Strpos example

DECLARE
t_chars_original := 'Python is a popular scripting language';
BEGIN
  SELECT
  i_position_of_chars := STRPOS(t_chars_original, "is");
END

The SQL above uses Postgres’ Strpos() to return “8” because “is” begins at character 8 within the original string of “Python is a popular scripting language”.

Another example of Strpos in Postgres

If we make a small change to our script, we see an example of a PostgreSQL Substr use that many programmers will use at some point, which is to search for the first instance of a space within a string, so that we can find the position of the word(s) surrounded by those spaces:

DECLARE
t_chars_original := 'Python is a popular scripting language';
BEGIN
  SELECT
  i_position_of_chars := STRPOS(t_chars_original, " ");
END

In the example above, our STRPOS() function returns a 7 because the first space to be found in the string “Python is a popular scripting language” is at character number 7.

Let’s look at another 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 column that has multiple words.

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

id_techt_name_techt_cat
0OracleSQL Proprietary
1MongoNoSQL Flexible
2PostgreSQLSQL Free
3MySQLSQL Free

If our goal to return the first word in the t_cat column in tbl_technologies, we could use the LIKE with “%” operators but we need 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 with “%” method won’t work for what we are trying to do.

SELECT
  STRPOS(t_cat, " ") AS i_position_of_first_space
FROM
  tbl_technologies;

The above query will return the following results:

i_position_of_first_space
3
5
3
3

Organizing coding and database technologies

Here is the full “tbl_technologies” table.

id_techt_name_techt_cat
0OracleSQL Proprietary
1MongoNoSQL Flexible
2PostgreSQLSQL Free
3MySQLSQL Free

The example above that returns only one column of data may require an id or name of what tech is being referred to. So we’ll modify the query to provide the necessary information.

SELECT
  id_tech
  , t_name_tech
  , STRPOS(t_cat, " ") AS i_position_of_first_space
 FROM
  tbl_technologies;

Here are the results Postgres returns; id_tech, t_name_tech, and i_position_of_first_space for each record:

id_techt_namei_position_of_first_space
0Oracle3
1Mongo5
2PostgreSQL3
3MySQL3

The query above tells us where the first space is in each category (t_cat), which means we know the first word is defined by position 1 through i_position_of_first_space – 1.

Analysis

  • The STRPOS() function returns i_position_of_first_space.
  • Subtract 1 from that integer (i_position_of_first_space), so we now have the length of that first word.
  • Now that we know the parameters needed by SUBSTRING, we can extract the first word.

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

id_techt_name_techt_cat
0OracleSQL Proprietary
1MongoNoSQL Flexible
2PostgreSQLSQL Free
3MySQLSQL Free
SELECT
  id_tech
  , t_name_tech
  , STRPOS(t_cat, " ") AS i_position_of_first_space
  , SUBSTRING(t_cat, 1, i_position_of_first_space - 1)
 FROM
  tbl_technologies;

Postgres Position function

Another method to get the same word search done in Postgres uses the reverse, position, and substring functions to accomplish the same goal of finding and isolating the last word in a column:

SELECT
  id_tech
  , t_name_tech
  , REVERSE(t_cat) AS t_reversed
  , POSITION(" " IN t_reversed) AS i_position_of_space
  , SUBSTRING(t_cat, 1, i_position_of_space - 1) AS t_cat_last_word_reversed
  , REVERSE(t_cat_last_word_reversed) AS t_cat_last_word
 FROM
  technologies;

Analysis

(1) REVERSE(t_cat) as t_reversed creates a variable called “t_reversed”. The REVERSE function converts a hypothetical column value of “SQL Proprietary” into “yrateirporP LQS” and stores that string of characters in “t_reversed”.

(2) POSITION(” ” IN t_reversed) as i_position_of_space creates a variable called “i_position_of_space” and stores an integer there, which in this case is “12” because in our t_reversed string of “yrateirporP LQS”, the first space found by our POSITION() function is at character number 12. So now “12” is stored in “i_position_of_space”.

(3) SUBSTRING(t_cat, 1, i_position_of_space – 1) AS t_cat_last_word_reversed stores that first word, “yrateirporP” in a new variable called “t_cat_last_word_reversed”. So now we have our last word isolated but in reverse of what we need.

(4) REVERSE(t_cat_last_word_reversed) AS t_cat_last_word creates a variable called “t_cat_last_word”. The REVERSE function converted “yrateirporP” into “Proprietary” and stored that string as “t_cat_last_word”.

Notes

  • In many situations, like for example, arrays, we start the index at zero. So it is important to keep in mind that with SUBSTRING, we start counting the position with a “1”.
  • Not to be confused with the SubStr() function that is used in some database systems and languages. This was part of an older Oracle spec, too.

Conclusion

In this lesson we saw the use of different functions in Postgres to search a column and/or isolate a word or words, in this case the last word in a column of our test table. Some of the functions we used here include SUBSTRING, POSITION, STRPOS, and REVERSE. We used tested SQL code examples along the way.

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.