STUFF Function in Sql Server

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

One thought on “STUFF Function in Sql Server

  1. I like this STUFF with examples.

    If you could given an examples of practical or real time experience it would have made sense.

    You know the subject well please share and describe your practical experience to others.

    I like your Tips a lot.

    Thanks a bunch

Leave a Reply

Your email address will not be published. Required fields are marked *