Post
Ask Question

STUFF() And REPLACE() In SQL Server

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,

  1. Use Case
  2. STUFF Function
  3. Replace Function
  4. 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)

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.


Similar Articles