SQL Server: STUFF Function
This SQL Server tutorial explains how to use the STUFF function in SQL Server (Transact-SQL) with syntax and examples.
Description
In SQL Server (Transact-SQL), the STUFF function deletes a sequence of characters from a source string and then inserts another sequence of characters into the source string, starting at a specified position.
Syntax
The syntax for the STUFF function in SQL Server (Transact-SQL) is:
STUFF( source_string, start, length, add_string )
Parameters or Arguments
- source_string
- The source string to modify.
- start
- The position in the source_string to delete length characters and then insert add_string.
- length
- The number of characters to delete from source_string.
- add_string
- The sequence of characters to insert into the source_string at the start position.
Note
- See also the REPLACE function.
Applies To
The STUFF function can be used in the following versions of SQL Server (Transact-SQL):
- SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005
Example
Let’s look at some SQL Server STUFF function examples and explore how to use the STUFF function in SQL Server (Transact-SQL).
For example:
SELECT STUFF('TechOnTheNet.com', 1, 12, 'CheckYourMath');
Result: 'CheckYourMath.com'
SELECT STUFF('TechOnTheNet.com', 5, 2, '1234');
Result: 'Tech1234TheNet.com'
SELECT STUFF('TechOnTheNet.com', 13, 4, ' is a great site!');
Result: 'TechOnTheNet is a great site!'