CHR in CockroachDB
In this document, we will study the CHR in CockroachDB from multiple perspectives, including:
- What? What is the overview and syntax of the CHR function?
- Why? In what circumstances do we use the CHR function in our CockroachDB queries?
- How? How do we use the CHR function in our SQL?
- Similar functions We’ll also look at some functions that may complement CHR, such as ASCII.
- Additional concepts We’ll use some of the Python server-side scripting language as well as the Cockroach CASE statement.
In this document we are going to look at the counterpart for CHR, which is ASCII. As you will see, they are complementary.
intASCIIcode := ASCII(txtString)
txtCharacter := CHR(intASCIIcode)
The Cockroach ASCII function returns a string character based on a table of values. Because of copyright, we can not link to one of the many tables of ASCII/CHR codes and characters out there but a search will quickly yield one for you.
Why use CHR and ASCII?
While there are many uses for these two highly useful functions, some of the ways that all coders and database admins will reap benefits from CHR and ASCII are to have a way to refer to the single and double quote characters, usually nested in other single or double quotes.
In most every modern coding language,
" are used to delineate text data types. We’ll show you how this can sometimes be a problem when your text includes one of those two characters and how using either the CHR function in CockroachDB or the ASCII function in CockroachDB can be used to make this a non-problem.
We will begin by looking at some Python code where we want to include the quote character in an SQL script we are writing:
First, we will make sure you have in mind that the ASCII code for
"is 34 and the ASCII code for
'is 39. We’ll be using those two codes throughout this document to get around the issue of directly typing those pesky text delineation characters.
We’ll begin with using the ASCII function to UPDATE our database with a word that includes the
' character. In other words, let us say we want to convert all instances of “We will” to the contraction “We’ll”.
Before getting into building that CockroachDB SQL script, we will set up a simple text string of that contraction word using Python:
txtWord = 'We' + CHR(39) + 'll'
txtWord now has a value of “We’ll” because we’ve combined “We” with the ASCII code for the “‘” symbol, used the CHR function to insert that symbol, and then concatenated “ll” to the end using Python’s “+” symbol.
Now, we will show you how this works in the text string SQL we are building using Python in order to update a CockroachDB database table.
# UPDATE from non-contraction to contraction
s = ""
s += "UPDATE tblUsers"
s += " SET"
s += " txtWhoWill = 'We" || CHR(39) || "ll'"
s += " WHERE"
s += " ("
s += " txtWhoWill = 'We will'"
s += " )"
Analysis of the above code
- UPDATE: Establishing which table we are going to update; in this case the “tblUsers” table.
- SET: This is the foundation of our test project. It’s where we use the CHR(39) code, which creates the “‘” character. We’re telling Cockroach here to, “Build a word for us. Start with “We”, add “‘”, and then add “ll” to the end. Notice how the word we are building is encapsulated – as a standard SQL practice – within “‘” symbols? This is why we need the CHR function here.
- WHERE: Constraining / filtering our data set to update so that only the text phrase of “We will” is replaced with the text string we built above in the SET section above.
The code above changes the data in this table
Note that every case of “We will” was changed to “We’ll” but no change was made to “They will” and “I will”.
Let’s enjoy ourselves while learning about these two functions, ASCII and CHR, a bit more deeply and getting a bit more “real world” with our example!
ASCII and CHR example
As you may be already thinking, we will need a list of “contraction-able” words to use in our Cockroach SQL. We can choose from many ways to do this. We’ll throw in use of Cockroach CASE branching. We could instead choose multiple IF ELSE branches or to loop through the array in some Python code to rerun many queries, but the more efficient route is to do it all in Cockroach SQL with CASE, which is what we will do in this example.
First, a reminder of what our original data looks like:
Next we will start by examining that data and picking out the “contraction-able” words: “we will”, “they will”, and “I will”. You and I both know there are many other “contraction-able” words, but really, the way we will set up our code is so that you can easily add as many as you want to the word list. Best now, for brevity- and simplicity- sake, to just use the three we see here.
Let us use a simple (and somewhat inelegant) solution:
txtWhoWill = txtWhoWill_contracted
WHEN txtWhoWill = 'We 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 txtWhoWill_contracted
- UPDATE: Here we are designating which Cockroach table we want to modify; tblUsers.
- SET: We’re telling CockroachDB here that we want to set the value for the current row of the txtWhoWill column to be equal to the results given us in the CASE statement that follows.
- CASE: Here we are choosing from three different potential values that may be found in the “txtWhoWill” column, and depending on those values, we build our new replacement string using concatenation and the CHR function. Remember, here that CHR(39) gives us the
'(apostrophe) text character.
- END AS: Tells the Cockroach SQL engine what to call the results of our CASE … WHEN clause.
Why did we call the above example “inelegant”? While the above example works fine for three cases of strings to be replaced, what if our list were more like 10 or even 100? Would you want to have 100 CASE … WHENs handling that? Luckily, there are quite a few ways we could deal better with that situation. Because this document is about ASCII and CHR, we will settle with merely advising that you look into arrays and views.
In this lesson document we learned how and why to use use the CHR function in Cockroach SQL. We also used the ASCII function and CASE branching in order to try out different choices in our example SQL. Code samples included in the article above.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started