It's an interesting question,

But it also brings up a rather nasty fact about the language - a gaping whole in the whole idea that NULL is NULL, not somethink like 0 or an empty string or a mindnight at the end of December 31st 1899 according to what type null has. I agree with Toreador:

Of course, I suspect he's exagerating a little when he says it confuses him still - it seems far more likely that it merely irritates him now, that he has a "bitten once I won't be caught again" attitude to it.

The explanation gives no hint as to why the second answer is what it is.

Of course people get it right, either because they've been messed up by this before (probably only those of us who have done much text-manipulation in SQL, which probably won't be many - too many people have been taught that anything like that is violating some professor or other's imaginary basic principles of modularity) or because they run the code to see what happens.

In fact a null as the last parameter of stuff is always treated as a zero length string, not as a null, whether it's being appended to an initial (proper) substring of the original string, being prepended to a final (not neccessarily proper) substring of the original string, being interposed between non-intersecting initial and final substrings of the original string, or just replacing the whole of the original string. I can't see the point of having this definition (and since this silly mess takes a lot of coding round, I could see a lot of point in treating NULL as NULL always instead). It appears to conflict quite heavily with CONCAT NULL = NULL which is true by default in SQL Server (ANSI null handling). It is nowhere (as far as I can tell, apart from occassional screams of pain on the web) documented. There doesn't seem to be any point to it (except, perhaps, to hand ammunition to the anti-null fanatic fundamentalists).