Introduction
In this article, we are going to discuss STUFF and REPLACE functions in SQL Server. Also, we will discuss the differences between both of them and in which scenario it will be used.

We will cover,
    Use Case
    STUFF Function
    Replace Function
    Differences between STUFF and REPLACE

USE CASE
First, let's discuss the below use case,
Suppose we have a requirement to replace part of the string with a new string what you will do?

Your answer would be, REPLACE Function. Right?
Let me make this scenario more complex, suppose you have a string that has multiple words that are the same and you want to replace a word from a specific position. I mean only one word not all. How will you do that?

The answer would be, STUFF function.

Replace function will replace all the words so it will not fit in this case.

This is a difference between STUFF and REPLACE. REPLACE function replace all words from the text and STUFF will Replace word from a specific place.
STUFF Function in SQL Server

In SQL Server, the STUFF function deletes the sequence of the characters from the source string first and then inserts another string, starting at a given position.

Syntax
STUFF( source_string, start_position, length, another_string)

SQL
Source_string

The source string we will modify.
start_position

The start position is to delete characters from the source string.

Length

Number of characters to delete from the source string.

another_string
new string which will insert into the source string

Example 1 – Delete old string and insert a new string
Suppose I have a string "Hello All. Welcome and Nice to see you All in this article", I want to replace first ‘All’ with ‘World’.
SELECT  STUFF('Hello All. Welcome and Nice to see you All in this article',7,3,'World')

OUTPUT

Example 2 – Insert new string
I want to add a new string after "Hello" in the below example hence we have given 0 for length.
SELECT  STUFF('Hello . Welcome and Nice to see you All in this article',7,0,'World')

Example 3 – Format Date
We will change the date format from DDMMYYYY to DD/MM/YYYY using the STUFF function. This will teach you how to add nested STUFF.
SELECT STUFF(STUFF('23112021', 3, 0, '/'), 6, 0, '/') as FormattedDate;

REPLACE Function in SQL Server
The Replace function replace all occurrence within the string with a new string.

Syntax
REPLACE(original string, old_string, new_string)

Original string
This is the original string.

old_string
The string is to be replaced.

new_string
The new replacement string.

Example 4
We have replaced ‘All’ with ‘’world’.
SELECT REPLACE('Hello All. Welcome and Nice to see you All in this article','All','World')

Difference between STUFF and REPLACE
To understand the difference please see example 1 and example 4 or see below code,
SELECT  STUFF('Hello All. Welcome and Nice to see you All in this article',7,3,'World') as STUFFRESULT
SELECT REPLACE('Hello All. Welcome and Nice to see you All in this article','All','World') as REPLACERESULT

OUTPUT

In the above example, you can notice that REPLACE function replaces all the occurrences in the original string, but the STUFF function deletes a substring from the original string and inserts a new string at a given position.

I hope you enjoyed this article and find it useful.

HostForLIFEASP.NET SQL Server 2019 Hosting