Stuff is a function in SQL Server used to perform special operations on a string value.
The below operations can be performed,
- Remove string part from string expression.
- Insert/Append string at specified index.
Syntax
select STUFF(string_value, start_index, no_of_chars_to_replace, replace_string);
Remove String Part
select STUFF('hai_hello',0,2,'');
Important Note
Start Index begins from 1 in STUFF Function.
Proper Index
select STUFF('hai_hello',1,2,'');
Insert String Content
You can insert a string content by specifying the index location and set number of characters to replace to zero. Note that the third parameter value should be zero.
DECLARE @testString varchar(3) = 'abc';
select STUFF('hai_hello', 1, 0, @testString);
Replace String Content
You cannot replace string by specifying the old characters here.
But you can replace the string by specifying start location and number of characters to replace.
Note
The third parameter value should be the length to replace.
Example 1
DECLARE @testString varchar(3) = 'abc';
select STUFF('hai_hello', 1, DATALENGTH (@testString), @testString);
Example 2
select STUFF('hai_hello',1,2,'abc');
HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.
