T-SQL Tutorial T-SQL Advanced Database Management T-SQL References

T-SQL STUFF() Function



The T-SQL (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

string Required. Specify the string to modify.
position Required. Specify the position in string to insert substring.
length Required. Specify the number of characters to replace in string.
substring Required. Specify the substring to insert into string.

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, 'T-SQL');
Result: 'Learning T-SQL 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:

EmpIDNameCitySalary
FIN001JohnLondon3000
FIN002MarryNew York2750
FIN003JoParis2800
FIN004KimAmsterdam3100
FIN005RameshNew Delhi3000
FIN006HuangBeijing2800

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:

EmpIDNameCitySalary
FINCAD001JohnLondon3000
FINCAD002MarryNew York2750
FINCAD003JoParis2800
FINCAD004KimAmsterdam3100
FINCAD005RameshNew Delhi3000
FINCAD006HuangBeijing2800

❮ T-SQL Functions