What is the STUFF and how does it differ from the REPLACE function?
Both STUFF and REPLACE are used to replace characters in a string.
select replace('abcdef','ab','xx') results in xxcdef
select replace('defdefdef','def','abc') results in abcabcabc
We cannot replace a specific occurrence of “def” using REPLACE.
select stuff('defdefdef',4, 3,'abc') results in defabcdef
where 4 is the character to begin replace from and 3 is the number of characters to replace.
What is the STUFF and how does it differ from the REPLACE function?
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
