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_expressionstartlengthreplacement_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