This article explains the string function STUFF with extensive list of examples.

Description:

STUFF function adds the Addon_String into the Source_string at the specified Start position in the Source_String. Before adding the AddOn_String into the Source_string, it deletes the number of characters specified by the Length parameter from the Start position in the Source_string.

Syntax:

STUFF(Source_String, Start, Length, AddOn_String)

Examples:

Example 1:

SELECT STUFF('SqlServer.com', 4, 6, 'Hints') 'STUFF RESULT'

RESULT:
STUFF RESULT
———————–
SqlHints.com

Example 2:

SELECT STUFF('Sql.com', 4, 0, 'Hints') 'STUFF RESULT'

RESULT:
STUFF RESULT
———————–
SqlHints.com

Example 3:

SELECT STUFF('Hints.com', 1, 0, 'Sql') 'STUFF RESULT'

RESULT:
STUFF RESULT
———————–
SqlHints.com

Example 4:

SELECT STUFF('#SqlHints.com', 1, 1, '') 'STUFF RESULT'

RESULT:
STUFF RESULT
———————–
SqlHints.com

Example 5:

If Start position is larger than length of the first string, a null string is returned

SELECT STUFF('SqlHints', 9, 0, '.com') 'STUFF RESULT'

RESULT:
STUFF RESULT
———————–
NULL

Example 6:

If Start position is 0, a null value is returned.

SELECT STUFF('Hints.com', 0, 0, 'Sql') 'STUFF RESULT'

RESULT:
STUFF RESULT
———————–
NULL

Example 7:

SELECT STUFF('SqlHints#', 9, 1, '.com') 'STUFF RESULT'

RESULT:
STUFF RESULT
———————–
SqlHints.com

Example 8:

SELECT STUFF('SqlHints#', 9, 8, '.com') 'STUFF RESULT'

RESULT:
STUFF RESULT
———————–
SqlHints.com

Example 9:

SELECT STUFF('SqlHints#', 9, 20, '.com') 'STUFF RESULT'

RESULT:
STUFF RESULT
———————–
SqlHints.com

Example 10:

If Length is negative, a null string is returned

SELECT STUFF('Sql.com', 4, -1, 'Hints') 'STUFF RESULT'

RESULT:
STUFF RESULT
———————–
NULL

Example 11:

If Start position is negative, a null string is returned

SELECT STUFF('Hints.com', -1, 0, 'Sql') 'STUFF RESULT'

RESULT:
STUFF RESULT
———————–
NULL