Quote_Literal in CockroachDB
In this tutorial document, we will learn about Quote_Literal in CockroachDB, including the following points of view:
- What? An analysis of and syntax for the Cockroach QUOTE_LITERAL() function?
- Why? In which situations do we use the QUOTE_LITERAL function in our CockroachDB SQL?
- How? How do we use the QUOTE_LITERAL function in our Cockroach databases?
- Similar functions We will also examine some Cockroach functions that may complement or be used in place of QUOTE_LITERAL(), especially CHR().
- Additional ideas We will use Python with the Cockroach concatenation shorthand symbols and the CASE branch statement.
The QUOTE_LITERAL function overview, syntax, and parameters
As you probably get, apostrophe (otherwise known as semi-quotes or single quotes) and quote symbols are used in CockroachDB and many coding languages to delineate strings of text and string-based commands. For example, a typical WHERE clause may look like WHERE txtName = ‘database’. Things can get tricky when we want to send actual apostrophes or quotes as part of our string. Like, if an apostrophe is part of a name, like so: Jimmo Threefist O’Brien. How would we include something like that in a WHERE clause where we use apostrophes to delineate? Stay tuned because we’re going to show you.
Before we continue onward, a valuable bit of data: There is more than one way to attain this feat, including “escaping”, use of the “chr()” function, use of the “format()” function, and the SQL “USING” command. An advantage to “CHR()” is ease and advantages to “USING” are performance and security, as it is parameterized. But you wanted to learn about the quote_literal function so that is what we will teach about – mostly – in this tutorial document. That said, we will first show how to use the “CHR” function to attain the same goal.
- “txtSourceString” is the string of text we gave the function that has a special character – like a single quote in the middle of our string – we require deal with. So if we supply our quote_literal function with a txtSourceString of “Jimmy Jumbo O’Reilly”, the result we get back is “Jimmy Jumbo O\’Reilly” where the apostrophe is “escaped” so that an apostrophe can be used to encapsulate the entire text phrase.
Why use the Quote_Literal function?
Believe it or not, there is no real reason right now to use it. This is a legacy function we actually recommend not using. Instead, as mentioned above, use a newer and more efficient and well-supported function:
The CHR function
Let’s start out by focusing on some Python scripts where we will include the quote character in an SQL script we are writing:
First, we want to be sure you know that the ASCII code for quote (“) is 34 and the ASCII code for apostrophe (‘) is 39.
We will start out with using the ASCII codes to UPDATE our database with a word that includes the apostrophe character. In other words, let’s say we want to convert all instances of “They will” to the “They’ll” contraction.
Before getting into writing the SQL code, let’s build a simple string of that contraction word using Python and a string variable we’ll call “txtWrd”:
# In Python
txtWrd = 'They' + CHR(39) + 'll'
-- In Cockroach PL/SQL
txtWrd := 'They' || CHR(39) || 'll';
txtWrd now has a value of “They’ll” because we’ve combined “They” with the ASCII code for the “‘” symbol, used the CHR() function to insert that apostrophe symbol, and then concatenated “ll” to the end of the string we are building.
Now let’s look at how this works in a string we’re building using Python in order to change values in a CockroachDB database table.
# UPDATE values to contraction
s = ""
s += "UPDATE tblUsers"
s += " SET"
s += " txtCo = 'They" || CHR(39) || "ll'"
s += " WHERE"
s += " ("
s += " txtCo = 'They will'"
s += " )"
- UPDATE: Instructing CockroachDB which table we want to update; in this instance, “users”.
- SET: Here we have used CHR(39), which generates the “‘” character. We’re asking Cockroach here to, “Build a string for us. Start with “They”, add “‘”, and then add “ll” to the end. Notice how the word we are building is encapsulated – as a standard SQL practice – within “‘” symbols? That’s why we need CHR here!
- WHERE: Filtering our data-to-change so only the phrase of “They will” is replaced with the text string we had built using the SET clause above.
The scripts above changes the data in the tblUsers Cockroach table
Note that every case of “They will” was changed to “They’ll”.
Now, let’s learn about the CHR function more by going more “business reality”!
The task for our mini-project: Compress space in our database by replacing all contraction-able words with contractions
First, we will require a list of words that we know contractions exist for. We will use the CockroachDB CASE clause. We might instead choose to loop through the array in some Python scripts to rerun many SQL, but the more efficient route is to do it all in SQL.
A reminder of what our original data looks like:
We will start out by focusing on the data and picking out the words that lend themselves to being changed into contractions, like “they will”, “we will”, “You will”, and “I will”. We know there are many other “contraction-ables”, but the way we will set up our scripts is so that you easily can add as many as you want to this list of words.
An easy solution:
Using Cockroach CASE
txtCo = txtCo_contracted
WHEN txtCo = 'I will' THEN 'I' || CHR(39) || 'll'
WHEN txtCo = 'They will' THEN 'They' || CHR(39) || 'll'
WHEN txtCo = 'We will' THEN 'We' || CHR(39) || 'll'
WHEN txtCo = 'You will' THEN 'You' || CHR(39) || 'll'
END AS txtCo_contracted
- UPDATE: Designating which CockroachDB table we want to modify; “tblUsers”.
- SET: Instructing Cockroach that we want to set the value for the current record of the “txtCo” column to be equal to the results given us in the following “CASE” statement.
- CASE: Here we choose from four different potential values that may be found in the “txtCo” 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 ‘ (single quote) symbol.
- END AS: tells Cockroach SQL what to call the results of the CASE clause.
In this tutorial document your knowledge grew about how the Quote_Literal function works in CockroachDB SQL, why and when not to use it, and why and when to instead use either the CHR function, “USING”, or even Format, depending on your specific needs. We also used the “||” symbol combination for string concatenation. We also used some Python. Finally, your knowledge grew about how to use the “CASE” statement in order to try different values in our test project.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started