STUFF function

Question

What is the STUFF function and how does it differ from the REPLACE function?

Jignesh Trivedi · Answer

hi, 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 Nayak · Answer

Hi Sanjoli, 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

STUFF function

Answers (2)