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

Stuff And Replace Functions In SQL Server

In this article, we will discuss stuff and replace functions in SQL server.

Software Requirement

Before you start reading this tutorial you should make sure that SQL server (2005 or above) is installed on your machine.

Prerequisite Knowledge

Before you go ahead, it is required that you should know: -

Basics about SQL server objects like table, views, functions etc.
What is the difference between user defined and system defined functions?

Replace Function

Replace function replaces all occurrences of a specified string value with another string value.

Consider a string – "This is the original string". Now our requirement is to replace every occurrence of "is" with "was". In the string there are 2 instances of "is". See the figure below

Click to Enlarge

These both occurrences will be replaced and the final string becomes "Thwas was the original string".

Syntax

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

In the above syntax: -

First parameter is the string to be searched
Second parameter is the string to be found
Third one is the string value which will be replaced

Stuff Function

Stuff function inserts a string into another string. First stuff function deletes specified number of characters from the starting position and then inserts the string to the start position. Let us consider the previous example again.

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

In the above syntax: -

First parameter is the string where the characters will be deleted and then inserted
Second parameter is the starting position from where string will get inserted
Third parameter is the number of characters to delete from start position
Fourth parameter is the new string which will inserted at the start position

Picture showing how the stuff function works in SQL server

Click to Enlarge

The output of the stuff expression would be "This was the original string".

There are couples of more examples which I want to show you here. That will help you to understand stuff method.

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 example above there is a space after the string hello so the length of string is 6. Now we have 6th 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