STUFF function in in SQL Server

Question

Hi Friends I want to know about STUFF function and how does it differ from the REPLACE function in SQL Server ?

Bipul tiwari · Answer

The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position. For more detail click below link http://www.agrini-tricks.in/Tutorial/STUFF-function-In-SQL.aspx

Akkiraju Ivaturi · Answer

The STUFF function is used to overwrite the characters of string. The Replace function is used to replace the occurance of a particular string with the specified string in all occurances. STUFF Syntax: STUFF (String, StartPos, LengthofReplaceChar, ReplaceString) String - String to be overwritten StartPos - Starting Position for overwriting LengthofReplaceChar - Length of replacement string ReplaceString - String to overwrite REPLACE Syntax: REPLACE (String, StringToReplace,StringTobeReplaced) String - Input String StringToReplace - The portion of string to replace StringTobeReplaced - String to overwrite

Jignesh Trivedi · Answer

hi, Stuff function can be used for delete a certain length of the string and insert a new string in the deleted place. STUFF ( InputString, start, length, ReplacedString ) replace function replaces all the occurrences of a string expression with a new string within an input string. REPLACE ( InputString , StringToReplace , NewString )

Nattudurai Eswaramurthy · Answer

HI, 1. STUFF() This function can be used for delete a certain length of the string and insert a new string in the deleted place. Syntax Code: STUFF ( InputString, start, length, ReplacedString ) Example Code: Select STUFF ("this is a test", 7, 2, "was") -- Result - this was a test 2. REPLACE() This function replaces all the occurrences of a string expression with a new string within an input string. Syntax: Code: REPLACE ( InputString , StringToReplace , NewString ) Example, Code: Select REPLACE ("This is a test and it is successful!","is","was") -- Result - This was a test and it was successful!

Nattudurai Eswaramurthy · Answer

HI, 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

Nattudurai Eswaramurthy · Answer

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

STUFF function in in SQL Server

Answers (6)