Stuff and Replace In SQL

Introduction

In this article i will explain about what is Stuff and Replace and what is use of both of them. Difference between stuff and replace. How to use stuff and replace in SQL.

What Stuff And Replace Is

Stuff and replace both are used for replace the existing string.In other words you can say that stuff insert a given string into another existing string and replace also did the same.

But  the only difference is stuff function insert at a specified range of a string while replace changed all the occurrence of a given string. Clearly shown in below examples: 

Stuff Syntax

STUFF ( InputString, start, length, ReplaceString)

Example

SELECT Stuff( 'romanrom', 2,  4, 'ti')

Then on result window we got 'rtirom' as a result.Stuff  starts removing  from 2nd  index and upto 4 next string including 2nd.

Replace Syntax

Replace( InputString, string_pattern, ReplaceString)

Example

SELECT replace ( 'romanrom', 'ro', 'ti') SELECT replace ( 'KPPLKPPKPJ', 'KP', 'J')

Then it gives a new string 'timanti' and 'JPLJPJPJ'  in result window . From the above output it is clearly understandable that replace function replace all the matching occurrence of a given string or character. While using Stuff function we can change the specified range of a string