TSQL - Stop the STUFF function treating NULL as the empty string - by Tom Thomson

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 668871 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 5/17/2011 5:16:45 AM
Access Restriction Public


A NULL as the second string parameter is treated as an empty string (''), so that for example 
STUFF('abc',2,1,NULL) delivers 'ac' instead of NULL.  This is quite unlike the treatment of a NULL string in other contexts, except when CONCAT_NULL_YIELDS_NULL is set OFF, which it normally will not be (since people will not want to disable indexed views and indexes on computed columns).
This is an undesirable situation - NULL should not be treated as an empty string when CONCAT_NULL_YIELDS_NULL is set ON.
Sign in to post a comment.
Posted by Microsoft on 5/19/2011 at 6:24 PM
Hello Tom,
Thanks for the feedback. I agree that the current behavior doesn't match that of the string concatenation for NULL propagation. Unfortunately, we cannot change the existing STUFF function behavior due to backward compatibility implications. We might consider adding the ANSI SQL OVERLAY function in the future based on more customer feedback but for now I am closing this as "won't fix".

Umachandar, SQL Programmability Team