The STUFF Function

Parameters#

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')

Executing this example will result in returning SQL Server Documentation instead of SQL Svr Documentation.

Using FOR XML to Concatenate Values from Multiple Rows

One common use for the FOR XML function is to concatenate the values of multiple rows.

Here’s an example using the Customers table:

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

In the example above, FOR XML PATH('')) is being used to concatenate email addresses, using ; as the delimiter character. Also, the purpose of STUFF is to remove the leading ; from the concatenated string. STUFF is also implicitly casting the concatenated string from XML to varchar.

Note: the result from the above example will be XML-encoded, meaning it will replace < characters with < etc. If you don’t want this, change FOR XML PATH('')) to FOR XML PATH, TYPE).value('.[1]','varchar(MAX)'), e.g.:

SELECT 
    STUFF( (SELECT ';' + Email
        FROM Customers 
        where (Email is not null and Email <> '')
        ORDER BY Email ASC 
        FOR XML PATH, TYPE).value('.[1]','varchar(900)'),
    1, 1, '')

This can be used to achieve a result similar to GROUP_CONCAT in MySQL or string_agg in PostgreSQL 9.0+, although we use subqueries instead of GROUP BY aggregates. (As an alternative, you can install a user-defined aggregate such as this one if you’re looking for functionality closer to that of GROUP_CONCAT).

Obtain column names separated with comma (not a list)

/*
The result can be use for fast way to use columns on Insertion/Updates.
Works with tables and views.

Example: eTableColumns  'Customers'
ColumnNames
------------------------------------------------------
Id, FName, LName, Email, PhoneNumber, PreferredContact

INSERT INTO Customers (Id, FName, LName, Email, PhoneNumber, PreferredContact)
    VALUES (5, 'Ringo', 'Star', 'two@beatles.now', NULL, 'EMAIL')
*/
CREATE PROCEDURE eTableColumns (@Table VARCHAR(100))
AS
SELECT ColumnNames = 
   STUFF( (SELECT ', ' +  c.name
FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.user_type_id = t.user_type_id
WHERE
    c.object_id = OBJECT_ID( @Table)
        FOR XML PATH, TYPE).value('.[1]','varchar(2000)'),
    1, 1, '')
GO

stuff for comma separated in sql server

FOR XML PATH and STUFF to concatenate the multiple rows into a single row:

  select distinct t1.id,
      STUFF(
             (SELECT ', ' + convert(varchar(10), t2.date, 120)
              FROM yourtable t2
              where t1.id = t2.id
              FOR XML PATH (''))
              , 1, 1, '')  AS date
    from yourtable t1;

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.

 Select FirstName, LastName,Email, STUFF(Email, 2, 3, '*****') as StuffedEmail From Employee

Executing this example will result in returning the given table