SQL Server provides the STUFF() function that enables you to insert a string inside another string.

The function also allows you to specify the number of characters (if any) to delete from the original string in order to fit the new string.

Example

SELECT STUFF('WaterMelon', 6, 0, 'Juicy');

Result:

WaterJuicyMelon

Here I inserted the string Juicy into the string WaterMelon.

In this case I gave the third argument a value of 0, which means no characters were deleted from the original string.

Delete Characters from the Original String

In this example I delete some characters from the original string.

SELECT STUFF('WaterMelon', 6, 3, 'Juicy');

Result:

WaterJuicyon

Negative/Zero Start Positions

If you provide a negative value or zero for the start position, null is returned.

SELECT STUFF('WaterMelon', -1, 3, 'Juicy');

Result:

NULL

When the Start Position is Too High

You’ll also get null if you provide a start position that’s longer than the original string.

SELECT STUFF('WaterMelon', 100, 3, 'Juicy');

Result:

NULL