Replace in CockroachDB
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(txtString_source, txtString_to_replace, txtString_replacement)
- 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
txtString_source := "Cat person, dog person. Either way, they are an animal lover.";
txtString_to_replace := "Either";
txtString_replacement := "Whatever";
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.
# 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
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
To revert back to our original data. Run this:
txtPronounAndAction = REPLACE(txtPronounAndAction, "They'll", "They 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:
txtWhoWill = txtWhoWillReduced
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
- 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.
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