Substring in TimescaleDB

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

Introduction

In this document, we’ll learn to use Substring in TimescaleDB, 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 TimescaleDB database SQL?
  • When do we want to use the POSITION and SUBSTRING functions? We study a use case.
  • We will also take a study the REVERSE function.

The first thing to do is set up a database instance. We prefer using ObjectRocket because we like the amount of value and efficiency they provide.

Create a TimescaleDB Database Instance on ObjectRocket

  • (1) Go to kb.objectrocket.com and “Log In”.
  • (2) Use left menu to “Create Instance”.
  • (3) In the “Name” field, add a descriptive name for your instance.
  • (4) Under “Select your service” choose the system you want to use from “CockroachDB, Elasticsearch, PostgreSQL, Redis, TimescaleDB, and MongoDB”.
  • (5) Choose the “Cloud Provider” and “Type” you want.
  • (6) Select “Version” and “Region”.
  • (7) Click the “GO TO STEP 2” button.
  • (8) Make a choice in the “Pick Your Flavor” section.
  • (9) Choose the “Capacity (Storage/Memory)”. Notice how this choice influences your total fee at the bottom right.
  • (10) Click the “GO TO STEP 3” button.
  • (11) Under “Add a Whitelist IP” pick “ALOW ANY IP” or “USE MY IP” or “Add my IPs later”.
  • (12) Choose between “Master” and “Replica” and click the “ADD” button.
  • (13) Now click the “CREATE INSTANCE” button at the bottom.

Before we start building queries, let’s look at how to use Flask’s psycopg2 to connect to a TimescaleDB instance at ObjectRocket.

Connect to TimescaleDB

1
2
3
4
5
6
7
8
9
t_dbname = "myTSdb"
t_name_user = "tsdbadmin"
t_password = "secret"
t_sslmode = "require"
t_host = "ingress.hkybrhnz.launchpad.objectrocket.cloud"
t_port = "4129"
connection_string = "dbname=" & t_dbname & " user=" & t_name_user & " password=" & t_password & " host=" & t_host & " port=" & t_port & " sslmode=" & sslmode
db_conn = psycopg2.connect(connection_string)
db_cursor = db_conn.cursor()

What’s SUBSTRING?

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

Substring Syntax

1
txtStringPiece = SUBSTRING(txtStringOriginal, intPositionBegin, intCharactersToUse);

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

  • The original string, which we call “txtStringOriginal,” above.
  • The position (starting from the left) that we need to start retrieving characters. We call this “intPositionStart,” in the syntax above. If we need to start out on the right and count left, there is a way to do that, which we’ll 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 := 'TimescaleDB is the best database system.';
BEGIN
  SELECT
  txtStringPiece = SUBSTRING(txtStringOriginal, 1, 11);
END

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

Let us examine a deeper example in SQL of how the SUBSTRING function works. Here, we need to search the database for the first word in a value kept in a column that may have multiple words.

Our test table with already filled-in data:

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

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

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

The above SQL executed on our TimescaleDB will return the following results:

txtCategoryType
Database
Database
Database
Database
Language
Language
Language

Now that you have a novice’s understanding 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
2TimescaleDBDatabase Relational Free Best
3PythonLanguage Newish Flexible Best
4PHPLanguage Old Pervasive
5JavaLanguage Pervasive

The first item you may notice about the first example above that returns only a piece 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’ll now modify that query:

Important note: If you’re familiar with normalization, you might study the above table and think to yourself, “I want 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’s a topic for another document. We need 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 records of results TimescaleDB 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
2TimescaleDBDatabase
3PythonLanguage
4PHPLanguage
5JavaLanguage

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

For efficiency and simplicity, we’ll examine a return of the contents of tblTechnologies:

idTechtxtNametxtCategory
0PostgresDatabase Relational
1MongoDatabase NoSQL Flexible
2TimescaleDBDatabase 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’ll walk through the logic:

  • When we examine the functionality of STRPOS(), we learn there is not a way to tell it to count from the end of our source string, so we want to think of another way to attain the task.
  • Two of the most popular methods to attain this task are (1) Using the REVERSE() function and (2) Splitting the source text into an ARRAY. To keep this document as easy, quick, and focused as possible, we’ll look at one; the reverse function:

REVERSE Function

A quick summary of the syntax of the REVERSE function in TimescaleDB:

Reverse Syntax

1
txtReversed = REVERSE(txtPhrase);

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

  • First: we now have the word we’re 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 TimescaleDB 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 TimescaleDB SQL:

(a) REVERSE(txtCategory) as txtReversed creates a temporary variable called “txtReversed”. The REVERSE() function converted “Database Relational Free” into “eerF lanoitaleR esabataD” and kept 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 kept 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 kept that string as “txtCategoryLastWord”.

Miscellaneous Notes

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

Conclusion

In this document we examined how to use the SUBSTRING function in TimescaleDB. We also learned via an example that mimics how someone in the “”business reality”” would use the substring function, along with some related functions, including POSITION and REVERSE. 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.