Strpos in CockroachDB

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

Introduction

In this lesson, we will explore how to use the Strpos in CockroachDB to isolate text in multi-word strings, including:

  • What? What does the CockroachDB STRPOS function do?
  • How? How do we use this function in our CockroachDB SQL commands?
  • Why? When do we need these functions? We’ve included a real world use case for your learning pleasure.

Some prerequisites

Knowledge of building SQL for CockroachDB (or Postgres, MS SQL Server, etc.) using the free Dbeaver tool or another flexible relational database administration tool for running SQL to get data or make changes to your Cockroach database.

What is STRPOS?

The CockroachDB STRPOS function returns the position of a nested substring. So if you provide the function with something like, “Cockroach 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

1
intStringPosition = STRPOS(txtStringOriginal, txtStringToFind);

STRPOS example

Next, if we want to copy a part out of one string, we give the STRPOS function two parameters:

  • The original string, which we named “txtStringOriginal,” above.
  • The string we want to find.

Let’s take another step by filling the STRPOS syntax with some data:

1
2
3
4
5
6
DECLARE
txtStringOriginal := 'Python is a sweet scripting language.';
BEGIN
  SELECT
  intStringPosition := STRPOS(txtStringOriginal, "is");
END

The example above returns “8” as an integer because “is” starts at the 8th character in the text containing “Python is a versatile coding language.”.

If we make a small change to the above SQL, we see an example of a Substr type of search that most programmers will use at some point in time; searching for the first instance of a space within a string, so that we can delineate a word:

1
2
3
4
5
6
DECLARE
txtStringOriginal := 'Python is a sweet coding language.';
BEGIN
  SELECT
  intStringPosition := STRPOS(txtStringOriginal, " ");
END

In the example above, our Cockroach STRPOS function returns a “7”.

Now look at a slightly deeper example of how STRPOS can work for us. We want to search our Cockroach table for the first word in the text stored in a column that may contain multiple words.

Our test table, named tblOurTechnologies, with data:

idItemtxtItemtxtMyCategory
0PostgresDB Relational Free
1MongoDB NoSQL Flexible
2CockroachDB Relational Free
3PythonLang Newish Flexible
4PHPLang Old Pervasive
5JavaLang Pervasive

With the goal being to return the first word in the txtMyCategory column of our table, we could use the LIKE and “%” operators but we want to learn STRPOS. Also, we want to find the numeric position of that first word and do not want to assume we know how long or what that word is, so the LIKE/”%” method won’t work in this case. Here’s the code:

1
2
3
4
SELECT
  STRPOS(txtMyCategory, " ") AS intPositionFirstSpace
FROM
  technologies;

The above query will return the following results:

intPositionFirstSpace
3
3
3
5
5
5

While the example above may help you understand better a method where we can use Cockroach’s STRPOS, the data returned above is not very useful because we are not getting any id-type columns, so that we can know which technology goes with which category. Keep reading to learn how.

Finding a word with STRPOS

Again, here is the table we named “tblOurTechnologies”:

idItemtxtItemtxtMyCategory
0PostgresDB Relational Free
1MongoDB NoSQL Flexible
2CockroachDB Relational Free
3PythonLang Newish Flexible
4PHPLang Old Pervasive
5JavaLang Pervasive

The idea we spoke of above about our first example that returns only one column of data that may not be so useful without an id or name of what database or coding technology is being referred to. So we will now modify that query now:

An SQL script that acquires the information we need from the CockroachDB would look like:

1
2
3
4
5
6
SELECT
  idItem
  , txtItem
  , STRPOS(txtMyCategory, " ") AS intPositionFirstSpace
 FROM
  technologies;

Here are the recordset of results Cockroach returns to us, giving us idItem, txtItem, and intPositionFirstSpace for each row:

idItemtxtItemintPositionFirstSpace
0Postgres3
1Mongo3
2CockroachDB3
4Python5
5PHP5
6Java5

Now we know where the first space is. That means we know the first word is defined by position 1 through position (intPositionFirstSpace – 1), right?

Now we will step through the logic of how to do this and then take it a step further:

  • Use STRPOS to get intPositionFirstSpace.
  • Subtract 1 from intPositionFirstSpace so we get the length of that first word.
  • Use SUBSTRING to isolate the new word.

Here’s the script to accomplish that goal. But first we will list the full contents of our original table named tblOurTechnologies, for your ease:

idItemtxtItemtxtMyCategory
0PostgresDB Relational Free
1MongoDB NoSQL Flexible
2CockroachDB Relational Free
3PythonLang Newish Flexible
4PHPLang Old Pervasive
5JavaLang Pervasive
1
2
3
4
5
6
7
SELECT
  idItem
  , txtItem
  , STRPOS(txtMyCategory, " ") AS intPositionFirstSpace
  , SUBSTRING(txtMyCategory, 1, intPositionFirstSpace - 1)
 FROM
  tblOurTechnologies;

Miscellaneous

Strpos has similar functionality but different syntax as CockroachDB’s Position function.

Conclusion

In this lesson document we learned to use STRPOS() with CockroachDB SQL. In part of this lesson, we used realistic examples. Finally, we explored some other powerful string related functions like SUBSTRING as well as the commonly used SELECT statement.

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.