Replace, Stuff and Sub String Function in sql server

Replace function:

Replace is used to replace the all occurrence of the given pattern in a string.

Syntax

Replace (string, String_Pattern, String_replaceement)

  

See the example

In the given example I have a string ‘Bagesh kumar singh’ and I want to replace the occurrence of ‘a’ to X.

Declare @str varchar(100) Declare @strRep varchar(10) Set @str='Bagesh Kumar Singh' Set @strRep='X' select REPLACE(@str,'a',@strRep)

See the Output

  

Latter ‘a’ is replace by ‘X’

Stuff Function:

Stuff is used to replace the part of the string with some other string.

  

See the example

Declare @str varchar(100) Declare @Replace_String varchar(10) Set @str='Bagesh Kumar Singh' Set @Replace_String='Rajesh' Select STUFF( @str , 1 , 6,@Replace_String)

String is ‘Bagesh Kumar Singh’ in this example I am replacing the string which is started from 1 and length is 6. It is replace by ‘Rajesh’

Sub String function

Sub string returns the parts of the string from the given string

Syntax

Substring (String,start,length)

Declare @str1 varchar(100) Set @str1='Bagesh Kumar Singh' Select Substring( @str1 , 1 , 6)

See the Output