Search This Blog & Web

Wednesday, August 12, 2009

Replacing characters from a String -- Stuff

We can concat and replace characters from a string in different ways. To understand lets have an example

Lets suppose I have a value

declare @str varchar(20)
select @str = '44556663333666251324'

I want this output 00006663333666251324 this means i want to replace first 4 characters.

Solution One:

Use substring to return all required characters and remove undesired ones.

set @str = substring(@str, 5, len(@str))

This is the output of above code 6663333666251324.
Now use + concate operator to add four 0000 in start

select '0000' + @str

and we have desired result 00006663333666251324

Second Solution:

but their is another efficent way to do this.

declare @str varchar(20)
select @str = '44556663333666251324'

select stuff(@str,1,4,'0000')

and we have same output 00006663333666251324

This SQL Server function used to replace string characters with any other characters. It has four parameters
1. String name
2. Starting replacement index
3. Ending replacement index
4. What are the new characters (replaced characters)

We can use this in find and replace requirements.
Post a Comment