I am going to continue my series of very short articles or tidbits on Transaction SQL string functions. I will exploring the REPLACE() function today.
The REPLACE() function takes a source string, a search string and a replacement string as input. For each occurrence of the search string found in the source string, change the value to the replacement string.
Again, I will be using my favorite movie title in this example. Who does not like the “Wizard of OZ?”.
The example below replaces the word “of” with the word “in”.
|
1 2 |
-- Change one word select replace('wizard of oz', 'of', 'in') as str_phrase |
|
1 2 3 4 5 |
output: str_phrase ------------ wizard in oz |
One thing to always worry about when using TSQL functions is how will it react with empty strings?
Using the empty string as a replacement string results in the search string being removed from the source string.
Using the empty string as a search string results in no change to the input string. Thus, the output string equals the input string.
|
1 2 3 4 5 |
-- Empty string as replacement select replace('1 2 3 4 5', ' ', '') as str_no_spaces -- Empty string as search returns source string select replace('1 2 3 4 5', '', ' ') as str_no_change |
|
1 2 3 4 5 6 7 8 9 |
output: str_no_spaces -------------- 12345 str_no_change -------------- 1 2 3 4 5 |
Another thing to worry about when using TSQL functions is how will it react with a NULL values? Using a NULL value in any of the input parameters results in a output of NULL.
|
1 2 3 4 |
-- Null returns Null select replace(NULL, 'of', 'in') as str_phrase select replace('wizard of oz', NULL, 'in') as str_phrase select replace('wizard of oz', 'of', NULL) as str_phrase |
|
1 2 3 4 5 |
output: str_phrase ----------- NULL |
Next time, I will be exploring the REPLICATE() function.
