SQL Server STUFF() Function

The SQL Server (Transact-SQL) STUFF() function inserts a substring into a string at a specified position for a certain number of characters.

If the specified position is not within the length of the string, this function will return a null string. If length is negative, a null string is returned. If length is longer than the string, deletion occurs up to the last character in the string. If length is zero, insertion occurs at position location and no characters are deleted.

Syntax

STUFF(string, position, length, substring)

Parameters

Return Value

Returns the modified string.

Example 1:

The example below shows the usage of STUFF() function.

SELECT STUFF('Learning SQL is fun', 10, 3, 'SQL Server');
Result: 'Learning SQL Server is fun'

SELECT STUFF('abcxyz', 4, 3, 'def');
Result: 'abcdef'

SELECT STUFF('ABCPQR', 4, 3, 'abc');
Result: 'ABCabc'

Example 2:

Consider a database table called Employee with the following records:

In the below query, the STUFF() function is used to change the records of EmpID column of the Employee table:

UPDATE Employee SET EmpID = STUFF(EmpID, 1, 3, 'FINCAD');

-- see the result
SELECT * FROM Employee;

This will produce the following result: