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
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