TSQL Handy STUFF Function

STUFF ( original_string, start_position , length , replaceWith_string )
Insert a string into another.
Start_position: position where the replace starts on the originalString
Length: # of character to be replaced(deleted) on the originalString
If start_position <= 0, then return NULL
If length < 0, then returns NULL
If start_position >  length of originalString, then returns NULL
If length >= length of originalString, will be deleted to 1st character of originalString.

/***************************************/
/* STUFF function rules with examples */
/***************************************/

DECLARE @original_str VARCHAR(50) = 'Hello World',/* length = 11 */ 
        @replaceWith_str VARCHAR(50) = 'ABC';
SELECT 
  -- If start_position <= 0, then return NULL 
  STUFF(@original_str, -1, 1, @replaceWith_str) AS output1, 
  -- If length < 0, then returns NULL  
  STUFF(@original_str, 1, -1, @replaceWith_str) AS output2,
  -- If length = 0, then returns @replaceWith_str will be inserted
  --      between start_position and start_position + 1 
  STUFF(@original_str, 2, 0, @replaceWith_str) AS output3, 
  -- If start_position >  length of originalString, then returns NULL 
  STUFF(@original_str, LEN(@original_str) + 1, 1, @replaceWith_str) AS output4, 
  -- If length >= length of originalString, will be deleted to 1st character of originalString.  
  STUFF(@original_str, 1, LEN(@original_str) + 1, @replaceWith_str) AS output5, 
  -- Notice that even though length of @replaceWith_str < length of @original_str. Does not matter because there's 10 character to be deleted from the @original_str 
  STUFF(@original_str, 1, 10, @replaceWith_str) AS output6,
  -- if replace with string is empty, this act as an earaser. This will delete the first 2 character of the original string  
  STUFF(@original_str, 1, 2, '') AS output7;

/*************************************************/

/* Mask a SSN. Format 123889999 to 123-88-9999 */

/************************************************/

DECLARE @SSN CHAR(9) = '123889999';
SELECT STUFF(STUFF(@SSN, 4, 0,'-'), 7, 0, '-') AS SSN

GO

/***************************************************/

/* Mask a SSN. Format 123889999 to XXXXX9999 */

/*************************************************/

DECLARE @SSN CHAR(9) = '123889999';
SELECT STUFF(@SSN, 1, 5, 'XXXXX') AS Last4_SSN
     , STUFF(@SSN, 1, 5, REPLICATE('X',5)) AS Last4_SSN;

/******************************/
/* Concatenate strings    */
/*****************************/

USE AdventureWorks2012;
GO
SELECT TOP(5) FirstName, LastName
, STUFF(FirstName + ' ', LEN(FirstName) + 1, 0, ' ' + LastName) AS Name
FROM PERSON.PERSON
ORDER BY FirstName, LastName;

Reference: BOL STUFF Function: http://technet.microsoft.com/en-us/library/ms188043.aspx