STUFF

Stuff is another TSql Function which is used to delete a specified length of characters within a string and replace with another set of characters. The general syntax of STUFF is as below :

STUFF(character_expression1, start, length, character_expression2)Character_Expression1 represents the string in which the stuff is to be applied. start indicates the starting position of the character in character_expression1, length is the length of characters which need to be replaced. character_expression2 is the string that will be replaced to the start position.

Let us take an example :


EG:-

select STUFF((
SELECT DISTINCT TOP 100 PERCENT
                                ',' + t2.team_name
                        FROM    team_master AS t2
                        ORDER BY ',' + t2.team_name
                        FOR XML PATH('')
  ), 1, 1, '')