{"id":3101,"date":"2014-11-29T03:20:09","date_gmt":"2014-11-28T21:50:09","guid":{"rendered":"http:\/\/sqlhints.com\/?p=3101"},"modified":"2015-07-18T02:24:24","modified_gmt":"2015-07-17T20:54:24","slug":"stuff-function-in-sql-server","status":"publish","type":"post","link":"https:\/\/sqlhints.com\/2014\/11\/29\/stuff-function-in-sql-server\/","title":{"rendered":"STUFF Function in Sql Server"},"content":{"rendered":"

This article explains the string function STUFF with extensive list of examples.<\/p>\n

Description:<\/h3>\n

STUFF function adds the Addon_String <\/em>into the Source_string <\/em>at the specified Start <\/em>position in the Source_String<\/em>. Before adding the AddOn_String <\/em>into the Source_string<\/em>, it deletes the number of characters specified by the Length <\/em>parameter from the Start <\/em>position in the Source_string<\/em>.<\/p>\n

Syntax:<\/h3>\n

STUFF(Source_String<\/em>, Start<\/em>, Length<\/em>, AddOn_String<\/em>)<\/p>\n

Examples:<\/h3>\n
Example 1:<\/h5>\n
SELECT STUFF('SqlServer.com', 4, 6, 'Hints') 'STUFF RESULT'<\/pre>\n
RESULT:<\/strong>
\nSTUFF RESULT
\n———————–
\nSqlHints.com<\/p>\n
Example 2:<\/h5>\n
SELECT STUFF('Sql.com', 4, 0, 'Hints') 'STUFF RESULT'<\/pre>\n
RESULT:<\/strong>
\nSTUFF RESULT
\n———————–
\nSqlHints.com<\/p>\n
Example 3:<\/h5>\n
SELECT STUFF('Hints.com', 1, 0, 'Sql') 'STUFF RESULT'<\/pre>\n
RESULT:<\/strong>
\nSTUFF RESULT
\n———————–
\nSqlHints.com<\/p>\n
Example 4:<\/h5>\n
SELECT STUFF('#SqlHints.com', 1, 1, '') 'STUFF RESULT'<\/pre>\n
RESULT:<\/strong>
\nSTUFF RESULT
\n———————–
\nSqlHints.com<\/p>\n
Example 5:<\/h5>\n
If Start<\/em> position is larger than length of the first string, a null string is returned<\/p>\n
SELECT STUFF('SqlHints', 9, 0, '.com') 'STUFF RESULT'<\/pre>\n
RESULT:<\/strong>
\nSTUFF RESULT
\n———————–
\nNULL<\/p>\n
Example 6:<\/h5>\n
If Start<\/em> position is 0, a null value is returned.<\/p>\n
SELECT STUFF('Hints.com', 0, 0, 'Sql') 'STUFF RESULT'<\/pre>\n
RESULT:<\/strong>
\nSTUFF RESULT
\n———————–
\nNULL<\/p>\n
Example 7:<\/h5>\n
SELECT STUFF('SqlHints#', 9, 1, '.com') 'STUFF RESULT'<\/pre>\n
RESULT:<\/strong>
\nSTUFF RESULT
\n———————–
\nSqlHints.com<\/p>\n
Example 8:<\/h5>\n
SELECT STUFF('SqlHints#', 9, 8, '.com') 'STUFF RESULT'<\/pre>\n
RESULT:<\/strong>
\nSTUFF RESULT
\n———————–
\nSqlHints.com<\/p>\n
Example 9:<\/h5>\n
SELECT STUFF('SqlHints#', 9, 20, '.com') 'STUFF RESULT'<\/pre>\n
RESULT:<\/strong>
\nSTUFF RESULT
\n———————–
\nSqlHints.com<\/p>\n
Example 10:<\/h5>\n
If Length<\/em> is negative, a null string is returned<\/p>\n
SELECT STUFF('Sql.com', 4, -1, 'Hints') 'STUFF RESULT'<\/pre>\n
RESULT:<\/strong>
\nSTUFF RESULT
\n———————–
\nNULL<\/p>\n
Example 11:<\/h5>\n
If Start<\/em> position is negative, a null string is returned<\/p>\n
SELECT STUFF('Hints.com', -1, 0, 'Sql') 'STUFF RESULT'<\/pre>\n
RESULT:<\/strong>
\nSTUFF RESULT
\n———————–
\nNULL<\/p>\n","protected":false},"excerpt":{"rendered":"
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: … Continue reading STUFF Function in Sql Server<\/span> →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[3],"tags":[447,448,321,986,692,691,58,442,445,443,444,446],"class_list":["post-3101","post","type-post","status-publish","format-standard","hentry","category-sql-server","tag-add-string-into-another-string","tag-adding-string-into-another-string-in-sql-server","tag-sql","tag-sql-server","tag-sql-stuff","tag-sql-stuff-function","tag-string-functions","tag-stuff","tag-stuff-function","tag-stuff-sql","tag-stuff-sql-server","tag-stuff-sql-string-function"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p3xNAz-O1","_links":{"self":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/3101","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/comments?post=3101"}],"version-history":[{"count":12,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/3101\/revisions"}],"predecessor-version":[{"id":3113,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/3101\/revisions\/3113"}],"wp:attachment":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/media?parent=3101"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/categories?post=3101"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/tags?post=3101"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}