Toreador (12/10/2012)
DavidBridgeTechnology.com (12/10/2012)
SELECT STUFF('123456', 1,3,NULL) -- "456" - SQL has found the first character, removed it and two next to it (3 in total) and inserted nothing into the string (because NULL is nothing)NULL is not nothing!
If your explanation were correct then the result of
select '456' + NULL
would be '456'. And it isn't, it's NULL (which is what you'd expect).
Actually, nothing is perhaps the best characterization of NULL we have. But the mistake is that nothing is not the same thing as an empty string. An empty string has a type of string, a length of zero and data consisting of zero characters. It is a definitive statement of zero characters, just like the integer 0 is not nothing, but a definitive statement of the number zero.
NULL is truly nothing: no data, no type, no reason.
Consider carts of apples. Like a cart of apples, a string is a collection of things (characters). So when you have a cart of apples, and you add a cart with no apples (empty string) you end up with the same cart of apples as before. But if you have a cart of apples, and introduce a vacuum (nothing), weird things happen. So SQL Server doesn't generally allow combining or comparing things with nothing, to prevent upsetting the apple carts.
But STUFF is one of those odd cases: it was introduced primarily to make things easier on code monkeys. We wanted a way to "stuff" information into otherwise generic strings like error messages without worring about complex things like NULL elimination or type conversion. So it allows weird things to happen, unless you break its own weird rules like requiring the indexes to relate to actual positions in the string.