Use of Postgres Replace function in SQL

Introduction

In this tutorial, we will learn how to use the Postgres REPLACE function using SQL from a few perspectives, including:

• What? What is the description and syntax of the REPLACE() function?
• Why? In what situations do we use the REPLACE function in our PostgreSQL queries?
• How? How do we use the REPLACE function in our SQL?
• Additional concepts We’ll also use some Python and the Postgres CASE statement. We’ll also use the CHR() function and string concatenation.

How the REPLACE function works

Syntax

REPLACE(t_string_source, t_string_to_replace, t_string_replacement)

Parameters:

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

Easy example of use of the Postgres Replace() function

The result here should be “Call it string, call it text. Whatever way, it’s only words.”

Why use the REPLACE function?

There are many uses for this function…

We’ll begin with using REPLACE to UPDATE our database. Our goal is to replace all instances of “We will” in the database with “We’ll”. Let’s show you how this works in a string we’re building using Python in order to update a PostgreSQL database table.

Analysis of the above code

• UPDATE: Setting the source data table to be “tbl_users”.
• SET: This is where we change the value in a column. In this case the column is “t_pronoun_and_action”. We’re telling Postgres here, “If you find “We will” anywhere within this column, replace it with “We’ll”.”
• 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

to become

Notice that every case of “We will” was been changed to “We’ll”? Now we will learn more about this function and get some more “real world” experience.

The activity: Save some database table bytes by replacing all contraction-able words with contractions

fIRST, we’ll need a list of “contraction-able” words to use in our SQL. We can choose from many different ways to do this. For this exercise, we will use the Postgres CASE branching clause. We could instead choose to 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 a more efficient route is to do it all in Postgres SQL, which is what we’ll do now.

First, to revert back to our original data. Run this:

Now we’ll begin this by examining the data in the “t_pronoun_and_action” column and picking 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 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.

Let’s use a simple solution:

Query Analysis

• UPDATE: Here we are telling Postgres which table we want to modify.
• SET: We’re directing PostgreSQL here to set the value for the current row of the “t_who” column to be filled with the results given us in the CASE statement that follows that is stored in the “t_who_shortened” column variable.
• CASE: Here we pick from three different potential values that may be in the “t_who” 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.
• END AS: Tells the Postgres engine what to call the results of our CASE … WHEN clause; “t_who_shortened”.

Why might we label the above example as “inelegant”? While the above example works great for three cases of strings to be replaced, what if our list were more like 100 or even 1000? Would you want to have 100 CASE…WHENs handling that? There are many ways we could better deal with that situation, including arrays or even better, subqueries or views. Because this article is about REPLACE, we’ll settle with merely advising that you look into the other means we mentioned.

Miscellaneous

Did you wonder why we named some columns (fields) and variables with a prefix of “i” or “t“? In this tutorial, we used “i” to mean integer and “t” to mean text or string. Here is a short tutorial that explains why, including how that practice can increase your efficiency as a programmer.

Conclusion

In this tutorial, we learned how and why to use the REPLACE() function in Postgres SQL. We also used the CASE branching statement in order to try different choices in our example query. Finally, we got to see the CHR() function and the “||” concatenation symbol in action.