STUFF Function

We looked at using the CONCAT function to concatenate or combine two or more string values. With this function, string values are appended at the end of the prior string but there are times when you want to insert a string value into another string value or replace a part of one string with another string. This is where you can leverage the STUFF T-SQL function.
Unlike the CONCAT function, which was introduced in SQL Server 2012, the STUFF function has been in prior versions of SQL Server too and hence you can use it in them too.
Based on your requirement, you can either insert a second string into the main string or replace a part of the main string with a second string. In that case it deletes a specified length of the characters in the main string at the specified start position and then inserts the second string into the main string at the specified start position. Here is the complete syntax of using the STUFF function:
STUFF (, , , )
The first select statement in the script below demonstrates how it inserts a second string into the main string at the specified start position whereas the second select statement replaces part of the main string with the second string.
SELECT STUFF(‘One match’, 5, 0, ‘day ‘)
GO
SELECT STUFF(‘One match’, 5, 4, ‘day ‘)