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
1 2 3 4 5 6 | DECLARE t_string_source := "Call it string, call it text. Either way, it's only words."; t_string_to_replace := "Either"; t_string_replacement := "Whatever"; SELECT REPLACE(t_string_source, t_string_to_replace, t_string_replacement); |
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.
1 2 3 4 5 | # UPDATE from non-contraction to contraction s = "" s += "UPDATE tbl_users" s += " SET" s += " t_pronoun_and_action = REPLACE(t_pronoun_and_action, "We will", "We'll") |
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
t_name_user | t_pronoun_and_action |
---|---|
Ted | We will |
Bif | They will |
Susan | I will |
Tim | We will |
Gary | We will |
Sammy | They will |
Frieda | They will |
to become
t_name_user | t_pronoun_and_action |
---|---|
Ted | We’ll |
Bif | They will |
Susan | I will |
Tim | We’ll |
Gary | We’ll |
Sammy | They will |
Frieda | They will |
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:
1 2 3 4 | UPDATE tbl_users SET t_pronoun_and_action = REPLACE(t_pronoun_and_action, "We'll", "We will") |
t_name_user | t_pronoun_and_action |
---|---|
Ted | We will |
Bif | They will |
Susan | I will |
Tim | We will |
Gary | We will |
Sammy | They will |
Frieda | They will |
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:
1 2 3 4 5 6 7 8 | UPDATE users SET t_who = t_who_shortened CASE 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_shortened |
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started