STUFF function is used to insert a string into another string by deleting some characters specified.


The function below inserts the string “nny” at the 2nd position and replaces a total of 3 characters.

Example:
SELECT STUFF('john', 2, 3, 'nny')

Output:
jnny

On the other hand, REPLACE instead of replacing specific characters, replaces existing characters of all occurrences.

Example:
SELECT REPLACE ('Johnnohneny','ohn','ccc');

Output:
Jcccnccceny