SQL Server STUFF() Function with example

The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position

STUFF() Function can be used in various ways. Let's see the below examples.

Basic Character Replacement with STUFF()

The STUFF() function inserts a string into another string by first deleting a specified number of characters. The following example, deletes "Svr" and replaces it with "Server". This happens by specifying the start_position and length of the replacement.

SELECT STUFF('SQL Svr Documentation', 5, 3, 'Server')

Output:

SQL Server Documentation

Basic Example of STUFF() function

STUFF(Original_Expression, Start, Length, Replacement_expression)

STUFF() function inserts Replacement_expression, at the start position specified, along with removing the characters specified using Length parameter.

Use the below table as example:

Table Name : ProductReview

ProductReviewID	ReviewerName	EmailAddress
1	John Smith	john@fourthcoffee.com
2	David	david@graphicdesigninstitute.com
3	Jill	jill@margiestravel.com
4	Laura Norman	laura@treyresearch.net

SQL Code:

Select ProductReviewID, ReviewerName, STUFF(EmailAddress, 2, 2, '*****') as StuffedEmail From ProductReview

Output:

ProductReviewID	ReviewerName	StuffedEmail
1	John Smith	j*****n@fourthcoffee.com
2	David	d*****id@graphicdesigninstitute.com
3	Jill	j*****l@margiestravel.com
4	Laura Norman	l*****ra@treyresearch.net

Using FOR XML to Concatenate Values from Multiple Rows

Below is the example, used above mentioned table. Reviewer name is separated by ;

SQL Code:

SELECT
STUFF( (SELECT ';' + ReviewerName
FROM ProductReview
where (ReviewerName is not null and ReviewerName <> '')
ORDER BY ReviewerName ASC
FOR XML PATH('')),
1, 1, '')

Output:

David;Jill;John Smith;Laura Norman