Articles → SQL SERVER → Stuff And Replace Functions In SQL Server

Stuff And Replace Functions In SQL Server

This article explains the stuff and replace functions in SQL server.

Software Requirement

Before reading this article, ensure that the SQL server (any version) is installed on your machine.

Prerequisite Knowledge

Before proceeding, it is essential to understand the following: -

The basics of SQL Server objects such as tables, views, functions, etc.
The difference between user-defined and system-defined functions

Replace Function

The replace function substitutes all instances of a specified string with another string.

Consider the following string – This is the original string. The requirement is to replace every occurrence of is with was. In the string, there are 2 instances of is. See the picture below.

Both occurrences will be replaced, resulting in the final string: Thwas was the original string.

Syntax

SELECT 
  REPLACE(
    'This is the original string', 'is', 
    'was'
  )

In the above syntax: -

The first parameter is the string to be searched.
The second parameter is the string to be found.
The third one is the string value which will be replaced.

Stuff Function

The stuff function inserts a string into another string. To insert the string, the stuff function first deletes the specified number of characters from the starting position and then inserts the string to the start position.

Consider the following example: -

select 
  stuff(
    'This is the original string', 6, 
    2, 'was'
  )

In the above syntax: -

The first parameter is the string from which characters will be deleted and the new characters will be inserted.
The second parameter specifies the starting position where the string will be inserted.
The third parameter is the number of characters to delete from the start position.
The fourth parameter is the new string that will be inserted at the start position.

Picture showing how the stuff function works in SQL server

The output of the stuff expression is - This was the original string.

More Examples For Stuff Function

select 
  stuff('hello', 6, 2, 'how are you')

The select statement will return NULL because the length of the string is 5 and there is no 6th position to insert string.

select 
  stuff('hello ', 6, 2, 'how are you')

In the above code, there is a space after the string hello so the string’s length is 6. Now we have the 6^th position to insert string, so this select statement will return hellohow are you.

Posted By -	Karan Gupta

Posted On -	Sunday, January 6, 2013

Query/Feedback

Your Email Id		**

Subject		*

Query/Feedback	Characters remaining 250	**