Updated March 14, 2023
Introduction to Redshift replace
Redshift replace function is provided in AWS redshift, which can help to replace the character or a string containing certain characters to the other character or set of characters as per requirement. In the case of replace function, we can make the substitution of all the occurrences of a particular character of set of characters forming the string in the source string with any of the expected values required in the target string.
In this article, we will learn about the syntax of replace function, how it works in Amazon Redshift and study how we can implement this function with the help of multiple examples. Finally, we will conclude our statement.
Syntax –
The syntax of the replace function is as shown below –
REPLACE (source string, characters to be replaced, characters with which to replace)
In the above syntax, the terminologies used are as described in detail one by one –
Source string – This is the main string which contains the characters to be replaced, and this is the same output string that we want but just including the characters replaced as per our requirement in it.
Characters to be replaced – This can either be single or multiple characters that are to be replaced from the source string. Note that all such occurrences in the source string need to be replaced from the source string. This character (s) needs to be either the CHAR datatype character or the VARCHAR datatype string.
Characters with which to replace – This can be a single character or set of characters having the datatype of CHAR or VARCHAR string which are to be placed in source string instead of the characters to be replaced.
Return Value – The value that is returned from the REPLACE function is a new string of VARCHAR datatype containing all the occurrences of old characters with the new ones. In case if any of the values of characters to be replaced or characters with which to replace is NULL, then the return value of replace function is also NULL.
How does REPLACE function works?
Though the replace function looks and works similar to that of the Redshift functions REGEXP_REPLACE function and TRANSLATE function, it is substantially different. In REGEXP_REPLACE function, the source string is searched to find a matching pattern with the specified regular expression and only checks the existence of such pattern in the string, while in the case of translate function, we can make the substitution of multiple single characters present in the source string with the other multiple target single characters by which we want the source characters to replace. In case of replace function, we can make the substitution of all the occurrences of a particular character of set of characters forming the string in the source string with any of the expected values required in the target string.
Examples of Redshift replace
Given below are the examples of Redshift replace:
Example #1
Let us consider one example where we have a table named educba_articles which contains all the information of the articles written for the organization. In order to check the contents of the table educba_articles, we can execute the following query statement –
SELECT * FROM educba_articles;
The output of the execution of the above query statement is as shown in the below image –
Suppose that we want to replace all the occurrences of the set of characters Redshift in the article name column to AWS. For this, we can make the use of REPLACE function in Redshift, and our query statement will have the source string mentioned as the name of the column article_name and the characters to be replaced and characters with which to replace are Redshift and AWS, respectively. Hence, our query statement will be as shown below –
select article_id, posted_date,
replace(article_name, 'Redshift', 'AWS')
from educba_articles
order by 1,2,3;
The output of the execution of the above query statement is as shown below –
Example #2
Let us now consider one more example where multiple occurrences of a particular character are replaced from the source string to a target character specified. We have one more table in our database having the name educba_writers that contains the information related to the writers of the organization and the details. To observe the contacts of the table, let us fire the below-mentioned query –
SELECT * FROM educba_writers;
The output of the execution of the above query statement is as shown in the below image –
As you can observe, we have one column with the name writer’s mail id, which contains the email address of the writers. Let us try to replace all the occurrences of the (.) dot with the (#) hash character. This will be done by using the replace function, and our query statement will now become as shown below –
SELECT writer_id as "Writer Identifier",
number_of_articles as "Contribution",
posted_date as "Date of Publishing",
REPLACE(writer_mail_id, ".", "#") as "Translated Email Address"
FROM [educba_writers];
The output of the execution of the above query statement gives out the following result shown in the below image. We can observe that all the occurrences of the dot operator are replaced with the hash operati=or in our source string writer mail id –
Example #3
Let us consider one more example where we will try to manipulate the string literal value having multiple occurrences of a set of characters. We have one string “EDUCBA is a great educational organization that generates huge knowledge and aids the educational system and educational aids.” In which we want to replace all the occurrences of the educational set of characters with the informative set of characters. For this, we can make use of the following query statement –
SELECT REPLACE ("EDUCBA is a great educational organization that generates huge knowledge and aids the educational system and educational aids.", "educational", "informative") as "Modified Statement";
The output of the execution of the above query statement is as shown in the below image –
Conclusion
The REPLACE function helps in the substitution of a particular character or set of characters from a particular expression or column value to the required character or set of occurrences. Note that all the occurrences of the specified characters are replaced from the source string.
Recommended Articles
This is a guide to Redshift replace. Here we discuss How does REPLACE function work along with the examples and outputs. You may also have a look at the following articles to learn more –
