Replace in CockroachDB

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

Introduction

In this lesson document, we will learn how to use Replace in CockroachDB using SQL. We are going to study the REPLACE function from a few perspectives, including:

  • What? A description and the syntax of the REPLACE function?
  • Why? In what situations do we use the REPLACE function in our CockroachDB queries?
  • How? How do we use the REPLACE function in our SQL?
  • Additional concepts We will also use some Python and the Cockroach CASE statement. We will also use the CHR function and string concatenation.

How the REPLACE function works

Below, we’ll take a step-by-step approach to learning some of the many uses of the Cockroach Replace function. We’ll begin with the basic syntax of this text-oriented function.

Replace syntax

1
REPLACE(txtString_source, txtString_to_replace, txtString_replacement)

Analysis:

  • txtString_source: The string we want to search through in order to find and replace a substring within that source text.
  • txtString_to_replace: The string we are looking for that is somewhere within the source string.
  • txtString_replacement: The string we want to use to replace the above “string to replace”.

Cockroach Replace() example

1
2
3
4
5
6
DECLARE
    txtString_source := "Cat person, dog person. Either way, they are an animal lover.";
    txtString_to_replace := "Either";
    txtString_replacement := "Whatever";
SELECT
    REPLACE(txtString_source, txtString_to_replace, txtString_replacement);

The result here should be “Cat person, dog person. Whatever way, they are an animal lover.”

REPLACE function use

There are many uses for this function, including:

We’ll start out by using REPLACE to UPDATE our Cockroach database. Our primary goal is to replace all instances of “They will” in the database with “They’ll”. Let us show you how this works in a string we are building using Python in order to update a CockroachDB database table.

1
2
3
4
5
# UPDATE from a non-contraction to a contracted version.
    s = ""
    s += "UPDATE tblDatabaseUsers"
    s += " SET"
    s += " txtPronounAndAction = REPLACE(txtPronounAndAction, "They will", "They'll")

Analysis of the above code:

  • UPDATE: Setting the source data table to be “tblDatabaseUsers”.
  • SET: This is where we change the value in a column. In this case the column is “txtPronounAndAction”. We’re telling Cockroach here, “If you find “They will” anywhere within this column, replace it with “They’ll”.”
  • WHERE: Constraining / filtering our data set to update so that only the text phrase of “They will” is replaced with the text string we built above in the SET section.

The code above changes the data in this table

txtEmployeetxtPronounAndAction
TadThey will
BudThey will
SusieI will
TamThey will
GeryThey will
SemmyThey will
FreidaThey will

to become

txtEmployeetxtPronounAndAction
TadThey’ll
BudThey will
SusieI will
TamThey’ll
GeryThey’ll
SemmyThey will
FreidaThey will

Notice that every case of “They will” was been changed to “They’ll” here? Now we will learn more about the REPLACE function and get some more “realistic business” experience.

Replace with contractions

First we will need a list of “contraction-able” words to use in our Cockroach SQL. We can choose from many different ways to do this. For this exercise, we will use the Cockroach CASE branching clause. We could instead choose to use IFs or loop through the array in some Javascript, PHP, or Python code to rerun many queries (each query being one of the contractions we want to replace), but the more efficient route is to do it all in Cockroach SQL, which is what we will do here next.

To revert back to our original data. Run this:

1
2
3
4
    UPDATE
        tblDatabaseUsers
    SET
        txtPronounAndAction = REPLACE(txtPronounAndAction, "They'll", "They will")
txtEmployeetxtPronounAndAction
TadThey will
BudThey will
SusieI will
TamThey will
GeryThey will
SemmyThey will
FreidaThey will

Now we will examine the data in the “txtPronounAndAction” column and pick out the “contraction-able” words: “we will”, “they will”, and “I will”. We know there are many other “contraction-able” words, but the way we want to set up our SQL is so that we can easily add as many to change as we want to the list of words and replacements. Best now, for brevity- and simplicity- sake, to use the method we see here, along with only three words to replace.

Here is a simple solution:

1
2
3
4
5
6
7
8
UPDATE users
SET
    txtWhoWill = txtWhoWillReduced
    CASE
        WHEN txtWhoWill = 'They will' THEN 'We' || CHR(39) || 'll'
        WHEN txtWhoWill = 'They will' THEN 'They' || CHR(39) || 'll'
        WHEN txtWhoWill = 'I will' THEN 'I' || CHR(39) || 'll'
    END AS txtWhoWillReduced

Query Analysis

  • UPDATE: Here we are telling Cockroach which table we want to modify.
  • SET: We’re directing CockroachDB here to set the value for the current row of the “txtWhoWill” column to be filled with the results given us in the CASE statement that follows that is stored in the “txtWhoWillReduced” column variable.
  • CASE: Here we pick from three different potential values that may be in the “txtWhoWill” column, and depending on those values, we build up our new replacement string using concatenation (“||”) and the CHR Function. CHR(39) gives us the (apostrophe) character in CockroachDB.
  • END AS: Tells the Cockroach engine what to call the results of our CASE … WHEN clause; “txtWhoWillReduced”.

Why would we call the above example “inelegant”? While the above example works great for three cases of strings to be replaced, what if our list were more like 2000 or even 20000? Would you want to have 300 CASEs handling that? There are many better ways we can deal with that situation, including arrays or even better, subqueries or views. Because this document is about REPLACE, we will settle with recommending that you look into the other means we have mentioned in this document.

Conclusion

In this lesson document, we learned how and why to use the REPLACE function in Cockroach SQL. We also used the CASE branching statement in order to handle different choices in our example query. Finally, we got to see how to use the CHR function and the “||” concatenation shorthand symbol.

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.