Wednesday, June 27, 2012

Replace vs Stuff in sql server 2005

The Replace function looks like this:
select replace('welcome in C# Corner','el','c')


Result: wccome in C# Corner 
No locations to find, just replace the string.  However, what if we had a string with two occurrences of the string, like:

select replace('Hello world','l','d')


Result: Heddo wordd 
If we only wanted to replace the first one, Replace wouldn't work, since it always replaces ALL occurrences of the string. But Stuff would, since it only replaces the string it finds at the starting location we tell it for the number of chars we want it to replace. 

select stuff('Hello world',7, 5,'Manish')
 
Result: Hello Manish

No comments: