Reverse in CockroachDB

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

In this lesson, we will learn to use Reverse in CockroachDB SQL for string manipulation, including for isolating characters in strings. The structure of this lesson is as follows:

  • What? What does the REVERSE function do in Cockroach and how do we use this function in our SQL execution?
  • Why? When do we need the Cockroach Reverse function? A realistic business use case is included in this document.
  • More We’ll also take a look at some closely related functions, including SUBSTRING and POSITION.

Prerequisites

Basic understanding of how to write SQL with CockroachDB, using the Dbeaver tool or by coding or server-side scripting languages like PHP, Python, Java, C#, ASP.Net, VB.Net, Node.js, etc. that allow a connection to your CockroachDB database as well as a method of sending SQL commands.

Cockroach REVERSE overview

This function changes the order of characters in a string to the opposite of what it was before. Example: “FUN” is turned to “NUF” by this function. Here is the syntax of the REVERSE function in CockroachDB:

Cockroach Reverse Syntax

1
txtReversed = REVERSE(txtPhraseOriginal);

It’s that simple. We’ll start our lesson by actually learning how to use a more complex function that we will use in conjunction with REVERSE later in a realistic business project.

SUBSTRING quickie

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

Syntax

1
txtStringPart := SUBSTRING(txtStringOriginal, intPositionStart, intCharactersToUse);

If we want to copy a piece out of one string into another, we give the SUBSTRING function these 3 parameters:

  • The original string, which we named “txtStringOriginal,” above.
  • The position (from left to right only right now) where we want to start grabbing characters from in the original string in CockroachDB. We call this “intPositionStart,” in the syntax section above. If we want to begin on the right and count left, there is a way to do that, which we will learn later.
  • Finally, we tell the function how many characters to use or grab; which we call “intCharactersToUse” above.

Give our SUBSTRING function some data:

1
2
3
4
5
6
DECLARE
    txtStringOriginal := 'Python is powerful and fun.';
BEGIN
    SELECT
    txtStringPart = SUBSTRING(txtStringOriginal, 1, 5);
END

The example above returns “Python” which is a string of the first 1 thru 5 characters from our original string.

Use REVERSE in Cockroach SQL

We’ll start out with an easy example. Our goal is to mine our Cockroach database for the first word in a value stored in a column that may have multiple words.

Our CockroachDB test table, “tblTechnologies”, with already filled-in some data:

idTechtxtNametxtCategory
2PostgresDatabase Relational Monolithic
3MongoDatabase NoSQL Flexible
4CockroachDBDatabase Relational Free Best
5PythonLanguage Newish Flexible Best
6PHPLanguage Old Pervasive
7JavaLanguage Pervasive

What if we want to return the first word in the txtCategory column of our Cockroach database? We could choose to use the LIKE and “%” operators but we are here to learn how to use REVERSE. Also, we want the first word and we can’t assume we know what that word will be, so this is where the LIKE and “%” method won’t serve our purpose. Here’s the script:

1
2
3
4
SELECT
  SUBSTRING(txtCategory, 1, 8) AS txtCategoryType
FROM
  tblTechnologies;

The above query will return the following results:

txtCategoryType
Database
Database
Database
Language
Language
Language

Now for a realistic business use case.

Categorizing technologies

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

idTechtxtNametxtCategory
2PostgresDatabase Relational Monolithic
3MongoDatabase NoSQL Flexible
4CockroachDBDatabase Relational Free Best
5PythonLanguage Newish Flexible Best
6PHPLanguage Old Pervasive
7JavaLanguage Pervasive

The first thing you may have noticed about our first CockroachDB substring example that returns only one column of data, values of 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 will now modify that Cockroach query:

The new SQL returns the information we need:

1
2
3
4
5
6
SELECT
  idTech
  , txtName
  , SUBSTRING(txtCategory, 1, 8) AS txtCategoryType
 FROM
  tblTechnologies;

Here is the recordset of results Cockroach returns to us, giving us characters 1 through 8 of the txtCategory column, results renamed to be “txtCategoryType” by CockroachDB’s “AS” operator you see above:

idTechtxtNametxtCategoryType
2PostgresDatabase
3MongoDatabase
4CockroachDBDatabase
5PythonLanguage
6PHPLanguage
7JavaLanguage

Let’s now see how changing the beginning position to a negative number provides us with a starting point at the end of our source value. In this case our reason for doing this is – using the “txtCategory” column – this time we want to get the last word in that phrase.

Again, here is the full dataset:

idTechtxtNametxtCategory
2PostgresDatabase Relational Monolithic
3MongoDatabase NoSQL Flexible
4CockroachDBDatabase Relational Free Best
5PythonLanguage Newish Flexible Best
6PHPLanguage Old Pervasive
7JavaLanguage Pervasive

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

  • When we study the functionality of the Cockroach 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 our objective.
  • Two of the more popular ways to accomplish this are (a) Using the REVERSE function and (b) Splitting the source string into an ARRAY. Since this document’s purpose is to learn the REVERSE function, we will go that route.

If the value stored in “txtPhrase” is “Database Relational Free” because we are right now at the row that is identified as idTech of 4, then txtReversed is filled with “eerF lanoitaleR esabataD”. How does that assist in accomplishing our objective?

  • 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” for the moment, as a first step.
  • The important part is that we now have that as our first word, so we can easily get it with the SUBSTRING function. THEN we can REVERSE it again to make it readable. Make sense? Don’t worry. We are going to take it step by step and explain every piece of this idea.

Now to create a query to extract that last word from the txtCategory column in our Cockroach table:

1
2
3
4
5
6
7
8
9
SELECT
  idTech
  , txtName
  , REVERSE(txtCategory) AS txtReversed
  , POSITION(" " IN txtReversed) AS intPositionOfSpace
  , SUBSTRING(txtCategory, 1, intPositionOfSpace - 1) AS txtCategoryLastWordReversed
  , REVERSE(txtCategoryLastWordReversed) AS txtCategoryLastWord
 FROM
  tblTechnologies;

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

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

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

(3) SUBSTRING(txtCategory, 1, intPositionOfSpace – 1) AS txtCategoryLastWordReversed stores that first word, “eerF” in a new temporary column called “txtCategoryLastWordReversed”. 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(txtCategoryLastWordReversed) AS txtCategoryLastWord creates a temporary column called “txtCategoryLastWord”. The Cockroach REVERSE function here converted “eerF” back into “Free” and stored that string as “txtCategoryLastWord”.

Conclusion

In this lesson we learned how to use the REVERSE function in Cockroach. We looked at the syntax, explained how it is used, and stepped through a realistic business example. Finally, we touched on some related functions such as SUBSTRING and POSITION. Code samples included.

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.