I am going to continue my series of very short articles or tidbits on Transaction SQL string functions. I will exploring the STUFF() function today.
The STUFF() function takes a source string [S], a starting position [P], a total length [L] and a replacement string [R] as input parameters. It removes [L] characters from the source string [S] starting at position [P] and replaces those changes with string [R].
From the definition of this function, one could easily write a custom user defined function with the same functionality using the SUBSTRING() function. This function has been part of the core language since SQL Server 2005.
The following example converts the favorite movie title of mine to upper case.
|
1 2 3 4 |
-- Correct call to function SELECT STUFF('Pay no attention to the man behind the curtain!', 5, 2, 'alot of') as oz_quote; |
|
1 2 3 4 5 |
output: oz_quote ------------ Pay alot of attention to the man behind the curtain! |
When a NULL value or empty string is used for the replacement string [R], the stuff function just removes the characters from the source string [S].
|
1 2 3 4 5 6 7 8 |
-- With expression - null & empty = remove characters SELECT STUFF('Pay no attention to the man behind the curtain!', 4, 3, '') as oz_quote; SELECT STUFF('Pay no attention to the man behind the curtain!', 4, 3, NULL) as oz_quote; |
|
1 2 3 4 5 |
output: oz_quote ------------ Pay attention to the man behind the curtain! |
One thing to always worry about when using TSQL functions is how will it react with empty strings or NULL values? Both empty strings and NULL values as input return NULL as output.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- Invalid start & length = null SELECT STUFF('Pay no attention to the man behind the curtain!', NULL, 3, 'alot of') as oz_quote; SELECT STUFF('Pay no attention to the man behind the curtain!', 4, NULL, 'alot of') as oz_quote; -- Source expression - null & empty = null SELECT STUFF('' , 5, 2, 'alot of') as oz_quote; SELECT STUFF(NULL , 5, 2, 'alot of') as oz_quote; |
|
1 2 3 4 5 |
output: oz_quote ------------ NULL |
To recap, if you need to replace one occurrence of a string [X] contained within string [S] with string [R], consider using the STUFF() function. If you need to replace multiple occurrences, use the REPLACE() function. Next time, I will be playing with the SUBSTRING() function.
