The STUFF function inserts a string into another string. It is most valuable for returning a list of values from a query as a single value when combined with the "FOR XML PATH" clause.

STUFF ( <text_expression> , <start> , <length> , <replace_with> ) 

STUFF is often used on combination with FOR XML PATH to retrieve a comma-delimited list (see below).

STUFF is often combined with "FOR XML PATH" to return a comma delimited list of values output as a single field. Here, the highlighted portion of the example represents a SELECT statement which returns a field (concatenated with a comma). The "FOR XML PATH" returns the results of the query as a single string. Using stuff in this way trims the excess comma from the query result.

STUFF((SELECT ', ' + <field> FROM <tables> FOR XML PATH('')),1,2,'') AS [Field_Name]