Reverse Function in Postgres SQL

Introduction

In this tutorial, we will learn to use the Reverse function in Postgres SQL to manipulate strings, to isolate words in multi-word text. We’ll also go over:

  • What? What does the REVERSE function do and how do we use this function in our SQL execution?
  • Why? When do we need the reverse() function? A real world use case is included.
  • Extra We’ll also take a look at some related functions, including SUBSTRING and POSITION.

Prerequisites

  • Basic knowledge of how to write SQL with PostgreSQL, using a PG Admin tool or by coding or scripting languages like PHP, Javascript, Python, Java, C#, ASP.Net, VB.Net, Ruby, Node, etc. that allows a connection to your database as well as a method of sending it SQL commands.
  • Understanding of the use of basic SQL statements, including SELECT and FROM.
  • Optional: Tutorial on naming conventions that explains why we prefix our variables, column names, table names, etc. (all objects, really) as you see us doing here. For example, naming “tphrase_original” with the “t” you see at the beginning in order to delineate it as a “text” object.

What does the REVERSE() function do?

This function changes the order of characters in a string to the opposite of what it was. Example: “WAM” is turned to “MAW” by this function. Here is the syntax of the REVERSE function in PostgreSQL:

t_reversed = REVERSE(t_phrase_original);

It’s that simple. We’ll begin our lesson by actually learning how to use a far more complex function that we’ll use in conjunction with Reverse() later in a mini project.

Quickie on a related function: SUBSTRING()

The PostgreSQL SUBSTRING function returns a part of a string. So if you seed the function with something like, “Postgres is super” and tell it you want the first 8 characters of that string, SUBSTRING("Postgres is super", 1, 8) returns “Postgres”.

Syntax

t_string_portion := SUBSTRING(t_string_original, i_position_begin, i_characters_to_use);

If we want to copy a chunk out of one string into another, we give the SUBSTRING function three parameters:

  • The original string, which we call “t_string_original,” above.
  • The position (from left to right only right now) where we want to begin grabbing characters from in the original string. We call this “i_position_begin,” in the syntax section above. If we want to start on the right and count left, there is a way to do that, which we’ll talk about later.
  • Finally, we tell the function how many characters to use or grab; which we call “i_characters_to_use” above.

Let’s give our function some data:

DECLARE
t_string_original := 'SQL is powerful and fun.';
BEGIN
  SELECT
  t_string_portion = SUBSTRING(t_string_original, 1, 3);
END

The example above returns “SQL” which is a string of the first 1 thru 3 characters of our original string.

Reversing back to reverse: How do we use the Reverse() function in Postgres SQL?

We’ll start simple. Our goal is to mine our database for the first word in a value stored in a column that may have multiple words.

Our test table, “technologies”, with already filled-in data:

id_techt_namet_category
0OracleDatabase Relational Monolithic
1MongoDatabase NoSQL Flexible
2PostgreSQLDatabase Relational Free Best
3MySQLDatabase Relational Free
4PythonLanguage Newish Flexible Best
5PHPLanguage Old Pervasive
6JavaLanguage Pervasive

What if we want to return the first word in the tcategory field (column) of our database? We _could use the LIKE and “%” operators but we’re here to learn how to use REVERSE. Also, we want the first word and we can’t assume we know what that word is, so this is where the LIKE and “%” method breaks down. Here’s the code:

SELECT
  SUBSTRING(t_category, 1, 8) AS t_category_type
FROM
  technologies;

The above query will return the following results:

t_category_type
Database
Database
Database
Database
Language
Language
Language

Getting closer to utilizing REVERSE

Mini project: Categorizing database and coding technologies

Again, here is the full table we named “technologies”.

id_techt_namet_category
0OracleDatabase Relational Monolithic
1MongoDatabase NoSQL Flexible
2PostgreSQLDatabase Relational Free Best
3MySQLDatabase Relational Free
4PythonLanguage Newish Flexible Best
5PHPLanguage Old Pervasive
6JavaLanguage Pervasive

The first thing you may have noticed about our first example that returns only one column of data, either “Database” or “Language” and that data is useless to us if not combined with at least an id or name of what tech is being referred to. So we’ll take baby steps and modify that query:

The new SQL returns the information we need:

SELECT
  id_tech
  , t_name
  , SUBSTRING(t_category, 1, 8) AS t_category_type
 FROM
  technologies;

Here is the recordset of results Postgres returns to us, giving us characters 1 through 8 of the t_category field (column), results renamed to be “t_category_type” by the “AS” operator you see above:

id_techt_namet_category_type
0OracleDatabase
1MongoDatabase
2PostgreSQLDatabase
3MySQLDatabase
4PythonLanguage
5PHPLanguage
6JavaLanguage

Let’s see how changing the start position to a negative number gives us a begin point at the end of our source data. In this case our reason for doing this is – again using the “tcategory” column – this time we want to get the _last word in that phrase.

Again, here is the full dataset:

id_techt_namet_category
0OracleDatabase Relational Monolithic
1MongoDatabase NoSQL Flexible
2PostgreSQLDatabase Relational Free Best
3MySQLDatabase Relational Free
4PythonLanguage Newish Flexible Best
5PHPLanguage Old Pervasive
6JavaLanguage Pervasive

Before we get into the SQL script for extracting the last word from that “t_category” column, let’s go through the steps:

  • When we study the functionality of the POSITION() function, we find there is no way to tell it to count from the end of our source string, so we need to think of another way to accomplish the goal.
  • Two of the more popular ways to accomplish this are (1) Using the REVERSE() function and (2) Splitting the source string into an ARRAY. Since this tutorial’s purpose is to learn the REVERSE function, we’ll go that route.

If the value stored in “t_phrase” is “Database Relational Free” because we are right now at the row that is identified as id_tech of 3, then t_reversed is filled with “eerF lanoitaleR esabataD”. How does that help accomplish our task?

  • Now we have the word we are looking for, which is the last word in our original data, i.e., “Free” but yeah, it’s reversed to be “eerF”.
  • The important part is that we now have that as our first word, so we can easily get it with our SUBSTRING function. THEN we can REVERSE it again to make it intelligable. Make sense? Don’t worry. We are going to take it step by step and explain every piece of that logic.

We can now create a query to extract that last word from the t_category column in our Postgres table:

SELECT
  id_tech
  , t_name
  , REVERSE(t_category) AS t_reversed
  , POSITION(" " IN t_reversed) AS i_position_of_space
  , SUBSTRING(t_category, 1, i_position_of_space - 1) AS t_category_last_word_reversed
  , REVERSE(t_category_last_word_reversed) AS t_category_last_word
 FROM
  technologies;

In case the above code seems confusing, we’ll explain line by line:

(1) REVERSE(t_category) as t_reversed creates a temporary variable called “t_reversed”. The REVERSE() function converted “Database Relational Free” into “eerF lanoitaleR esabataD” and stored that string in “t_reversed”.

(2) POSITION(" " IN t_reversed) as i_position_of_space creates a temp column called “i_position_of_space” and stores an integer in that column, in this case “5” because in our t_reversed string of “eerF lanoitaleR esabataD”, the first space found by our POSITION() function is at character number 5. So now the number “5” is stored in “i_position_space”.

(3) SUBSTRING(t_category, 1, i_position_of_space - 1) AS t_category_last_word_reversed stores that first word, “eerF” in a new temp column called “t_category_last_word_reversed”. So now we have our last word isolated but in reverse of what we need. Can you guess our last move? Yep, another reverse!

(4) REVERSE(t_category_last_word_reversed) AS t_category_last_word creates a temporary column called “t_category_last_word”. The REVERSE() function converted “eerF” into “Free” and stored that string as “t_category_last_word”.

Conclusion

In this tutorial we learned how to use the REVERSE() function in PostgreSQL. We looked at the syntax, explained how it is used, and stepped through a real world example. Finally, we touched on some related functions such as SUBSTRING() and POSITION().

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.