String Functions – STUFF()

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.

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].

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.

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.

Related posts

Leave a Comment