Use of ASCII and CHR Functions in Postgres SQL
In this article, we will study the ASCII and CHR functions in Postgres SQL from three perspectives, including:
- What? What is the overview and syntax of the ASCII() and CHR() functions?
- Why? In what circumstances do we use the ASCII and CHR functions in our PostgreSQL queries?
- How? How do we use the ASCII and CHR functions in our SQL?
- Similar functions We’ll also look at some functions that may complement ASCII() and CHR().
- Additional concepts We’ll use just a bit of Python as well as the Postgres CASE statement.
The ASCII and CHR functions and how they work
The Postgres ASCII function returns a string character based on the following table of values.
Since the table above, an extended ASCII/CHR table has arisen here: extended ASCII chart.
Why use the ASCII and CHR functions?
While there are many uses for these powerful functions, some of the ways that all programmers and database users can reap benefits from ASCII() and CHR() are to have a way to refer to the single and double quote characters, usually nested in other single or double quotes.
In pretty much every modern coding language,
' are used to delineate text (string) 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 ASCII or the CHR function can be used to make this a non-issue.
Let’s begin by looking at some Python code where we want to include the quote character in an SQL string we are building:
First, we’ll 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 article.
We’ll begin with using the ASCII code to UPDATE our database with a word that includes the
' character. In other words, let’s say we want to convert all instances of “We will” with “We’ll”.
First, before getting into building an SQL script, let’s build a simple text string of that contraction word using Python:
t_word 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 tacked “ll” to the end.
Now, we’ll show you how this works in a string we’re building using Python in order to update a PostgreSQL database table.
s = ""
s += "UPDATE users"
s += " SET"
s += " t_who = 'We" || CHR(39) || "ll'"
s += " WHERE"
s += " ("
s += " t_who = 'We will'"
s += " )"
Analysis of the above code
UPDATE: Establishing which table we are going to update; in this case the “users” table.
SET: This is the meat of our demo. It’s where we use the CHR(39) code, which creates the “‘” character. We’re telling Postgres 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? THAT 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.
The code above changes the data in this table
Note that every case of “We will” was changed to “We’ll”.
Let’s have some fun while learning about these powerful functions a bit more deeply and getting a bit more “real” with our example!
The goal: Save some database space by replacing all contraction-able words with contractions
As you may already be thinking, we’ll need a list of “contraction-able” words to use in our SQL. We can choose a few ways to do this. We’ll use the Postgres CASE clause. We could instead choose to loop through the array in some Python code to rerun many queries, but the more efficient route is to do it all in Postgres SQL, which is what we’ll do here.
First, a reminder of what our original data looks like:
Now we’ll start by looking at 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’s use a simple (and somewhat inelegant) solution:
t_who = t_who_contracted
WHEN t_who = 'We will' THEN 'We' || CHR(39) || 'll'
WHEN t_who = 'They will' THEN 'They' || CHR(39) || 'll'
WHEN t_who = 'I will' THEN 'I' || CHR(39) || 'll'
END AS t_who_contracted
UPDATE: Here we are designating which Postgres table we want to modify.
SET: We’re telling PostgreSQL here that we want to set the value for the current row of the t_who 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 “t_who” 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
END AS: Tells the 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? There are many ways we could better deal with that situation. Because this article is about ASCII and CHR, we’ll settle with merely advising that you look into arrays and views.
In this article we learned how and why to use use the ASCII() and CHR() functions in Postgres SQL. We also used the CASE construct in order to try different choices in our example query.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started