STUFF function
What is the STUFF function and how does it differ from the REPLACE function?
Loading
Know the answer? Post it — somebody with the same question will find it here.
Sign in to answer this question
It is the same account you read, post and publish with — and you will come straight back to this page.
Jignesh TrivediPosted Feb 1, 2012, 10:30 PM
STUFF function is used to overwrite existing characters.
Using this syntax, STUFF (string_expression,start,length,replacement_characters)
where
string_expression is the string that will have characters substituted,
start is the starting position,
length is the number of characters in the string that are substituted, and replacement_characters are the new characters interjected into the string.
REPLACE function to replace existing characters of all occurrences.
Using the syntax REPLACE (string_expression, search_string, replacement_string), where every incidence of search_string found in the string_expression will be replaced with replacement_string.
hope this help.
Satyapriya NayakPosted Feb 1, 2012, 7:44 AM
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
Thanks