Stuff

STUFF is another function that is hardly used, it is useful if you want to replace or add characters inside data Take a look at the code below. the first STUFF will replace X with 98765, the second STUFF will place 98765 before the X and the third stuff will replace X- with 98765
DECLARE @v VARCHAR(11)
    SELECT @v ='-X-'

    SELECT STUFF(@v, 2, 1, '98765'),
    STUFF(@v, 2, 0, '98765'),
    STUFF(@v, 2, 2, '98765')

The STUFF function is very handy if you need to insert dashes in a social security. You can accomplish that by using the function STUFF twice instead of using substring,left and right
DECLARE @v VARCHAR(11)
    SELECT @v ='123456789'

    SELECT @v,STUFF(STUFF(@v,4,0,'-'),7,0,'-')