Substring in CockroachDB

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

Introduction

In this document, we will learn to use substring in CockroachDB, along with the position and reverse functions for making changes to text with SQL to isolate strings. This lesson includes the following:

  • What do the POSITION and SUBSTRING functions do?
  • How do we use these functions in Cockroach database SQL?
  • When do we need to use the POSITION and SUBSTRING functions? We look at a use case.
  • We’ll also take a look at the REVERSE function.

What is SUBSTRING?

The CockroachDB SUBSTRING function returns a portion of a string. So if you seed the function with “CockroachDB is the best,” and tell it you want the first 11 characters of that string, the SUBSTRING() function will return “CockroachDB”.

Substring syntax

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

Analysis: if you want to copy a piece out of one text string, you feed the SUBSTRING function three parameters:

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

Now we take a step by filling our syntax with some data:

1
2
3
4
5
6
DECLARE
txtStringOriginal := 'CockroachDB is the best database system.';
BEGIN
  SELECT
  txtStringPart = SUBSTRING(txtStringOriginal, 1, 11);
END

The example above returns “CockroachDB” which is a string of the first 1 thru 11 characters of the original string.

Let us look at a deeper example in SQL of how the SUBSTRING function works. Here, we want to search our database for the first word in a value stored in a column that may have multiple words.

Our test table with already filled-in data:

idTechtxtNametxtCategory
0PostgresDatabase Relational
1MongoDatabase NoSQL Flexible
2CockroachDBDatabase Relational Free Best
3PythonLanguage Newish Flexible Best
4PHPLanguage Old Pervasive
5JavaLanguage Pervasive

How about when we want to return the first word in the txtCategory column of our Cockroach database? We could use the “%” and LIKE operators but we are here to learn how to use the SUBSTRING function. Also, we want to retrieve the first word and we don’t want to assume we know what that word is, so this is where the LIKE/% method will not serve our needs. Here’s the code:

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

The above SQL will return the following results:

txtCategoryType
Database
Database
Database
Database
Language
Language
Language

Now that you have a beginner’s knowledge of how the SUBSTRING function is used, we can get even more “real” with our lesson.

SUBSTRING for categorizing

Again, here is our table named “tblTechnologies”:

idTechtxtNametxtCategory
0PostgresDatabase Relational
1MongoDatabase NoSQL Flexible
2CockroachDBDatabase Relational Free Best
3PythonLanguage Newish Flexible Best
4PHPLanguage Old Pervasive
5JavaLanguage Pervasive

The first thing you may notice about the first example above that returns only a part of the second 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 technology is being pointed to. So we will now modify that query:

Important note: If you are familiar with normalization, you might look at the above table and think to yourself, “I need to normalize the table by designing a new one called ‘tblCategories’, and link it to tblTechnologies with a new column in tblTechnologies called idCategory.” That is a topic for another document. We want to keep this current lesson as simple as possible.

The new query that gives us the data we need:

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

Here are the rows of results Cockroach returns to us, returning characters 1 through 8 of the txtCategory column, results renamed to be “txtCategoryType” by the “AS” operator in the SQL above:

idTechtxtNametxtCategoryType
0PostgresDatabase
1MongoDatabase
2CockroachDBDatabase
3PythonLanguage
4PHPLanguage
5JavaLanguage

Now we will throw a twist in so we can show how changing the beginning position to a negative number gives us a start point at the end of our source text. In this case our reason for doing that is because – again using the “txtCategory” column – this time we want to extract the final word from the original text.

For efficiency and ease, we will look at a return of the contents of tblTechnologies:

idTechtxtNametxtCategory
0PostgresDatabase Relational
1MongoDatabase NoSQL Flexible
2CockroachDBDatabase Relational Free Best
3PythonLanguage Newish Flexible Best
4PHPLanguage Old Pervasive
5JavaLanguage Pervasive

Before we jump into the SQL script for extracting the last word from that category column, we will walk through the logic: – When we look at the functionality of STRPOS(), we see there is not a 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 most popular methods to accomplish this goal are (1) Using the REVERSE() function and (2) Splitting the source text into an ARRAY. To keep this document as easy, brief, and focused as possible, we will focus on one; the reverse function:

REVERSE function

A brief summary of the syntax of the REVERSE function in Cockroach:

Reverse syntax

1
txtReversed = REVERSE(txtPhrase);

Utilizing the syntax above, when the value in txtPhrase is “Database Relational Free” because we are at first looking at the row that has an idTech value of 2, then txtReversed is filled with “eerF lanoitaleR esabataD”. How does that move us toward our goal?

  • First: we now have the word we are looking for, which is the last word in our original data, i.e., “Free” but yeah, it is showing up right now as “eerF”.
  • The important thing is that we have it now as our first word, so we can easily get it with our SUBSTRING function before the next step, which is to use the REVERSE function on it again to turn it back into a recognizable word; “Free”.

Now we can build the SQL to extract that last word from the txtCategory column in our Cockroach database:

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;

Analysis of the above CockroachDB SQL:

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

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

(c) SUBSTRING(txtCategory, 1, intPositionOfSpace – 1) AS txtCategoryLastWordReversed stores that first word, “eerF” in a new temp variable called “txtCategoryLastWordReversed”. So now we have our last word isolated but in reverse of what we still need, which brings us to…

(d) REVERSE(txtCategoryLastWordReversed) AS txtCategoryLastWord creates a temporary variable we named “txtCategoryLastWord”. The REVERSE() function converted “eerF” into “Free” and stored that string as “txtCategoryLastWord”.

Miscellaneous Notes

In some situations, like with arrays, for example, we usually begin counting at zero instead of one. So it is important to keep in mind that with SUBSTRING(), we begin counting the first position with a “1”.

Conclusion

In this document we studied how to use the SUBSTRING function in CockroachDB. We also learned via an example that mimics how someone in the “real world” would use the substring function, along with some related functions, including POSITION and REVERSE.

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.