December 10, 2012 at 8:26 am
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.
December 10, 2012 at 8:34 am
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:
Toreador (12/10/2012)
The explanation is fine for the second example.But there's no mention of why a NULL value for 'replace with' returns a non-null result.
This confused me when I first came across it, and confuses me still!
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).
Tom
December 10, 2012 at 8:35 am
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).
This is what threw me. I expected STUFF to act like the addition operator since there was no mention of the behavior on MSDN. They should probably add this quarkiness to the documentation.
Without testing, how could one know.
Aigle de Guerre!
December 10, 2012 at 11:21 am
Thanks Ron
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 10, 2012 at 1:12 pm
Ron's QotDs are always interesting. Thanks, Ron!
December 10, 2012 at 1:58 pm
Good One!
December 10, 2012 at 2:44 pm
I agree with Toreador.
December 10, 2012 at 6:26 pm
rhythm.varshney (12/10/2012)
Nice question.However it will fetch error in SQL 2005 not sure about upper version as we can not assign value while declaration.
One could bypass this speed-bump with an explicit assignment:
DECLARE @STR VARCHAR(6)
Set @STR = 'ServeR'
DECLARE @stx VARCHAR(4)
But, more interestingly, I see that the doc for neither 2005 nor either 2008 include the "Start position = zero" case in their remarks section as does 2012. For that, I'll say I'm justified having run the code on our 2005 server to see what happens before answering. Looking at just the set-up in the daily email and then my 2005 BOL, I wondered if whether S-2 might return 'eR', having deleted positions 0, 1, 2, 3, and 4.
SQL 2005: http://msdn.microsoft.com/en-us/library/ms188043(v=sql.90).aspx
SQL 2008: http://msdn.microsoft.com/en-us/library/ms188043(v=sql.100).aspx
SQL 2008 R2: http://msdn.microsoft.com/en-us/library/ms188043(v=sql.105).aspx
December 10, 2012 at 7:03 pm
It occurs to me that we should not be confusing STUFF with arithmetic (string concat is a form of additive string arithmetic) with regards to the replaceWith argument.
yes, anything + NULL = NULL
However, when you think about it, what stuff is doing actually makes logical sense.
It is removing the appropriate number of characters from the string.
It is then inserting another string in those characters place.
In this scenario, a zero length string and NULL are functionally the same in that there is nothing to insert. Hence why the result is the same and you don't end up with any kind of NULL result.
That's my interpretation anyhows...
December 11, 2012 at 4:34 am
I 100% agree with all of you who commented on my earlier post.
Please note though..
Pedantically, NULL is not nothing but I said this in the descriptive rather than the literal. Stuff is a functon so it is up to the author how it is interpreted and its effect within the function and it appears that this function injects nothing if passed NULL.
I didn't say NULL is NULL as this doesn't really add a descriptive to what's happening. Also on this , whilst we are addressing the pedantic, you also cannot say NULL= NULL because NULL cannot be compared for equality.
I +1 the idea that Microsoft should fix BOL to show that a zero start location returns NULL as this would save time for newcomers to the function.
David Bridge
David Bridge Technology Limited
www.davidbridgetechnology.com
December 11, 2012 at 5:29 am
DavidBridgeTechnology.com (12/11/2012)
I 100% agree with all of you who commented on my earlier post.Please note though..
Pedantically, NULL is not nothing but I said this in the descriptive rather than the literal. Stuff is a functon so it is up to the author how it is interpreted and its effect within the function and it appears that this function injects nothing if passed NULL.
I +1 the idea that Microsoft should fix BOL to show that a zero start location returns NULL as this would save time for newcomers to the function.
I find it hard to get my mind round the concept that you think it appropriate to update BoL to specify that null is returned when lenth is 0, but not to document how the function treats null - in that case "it appears that" seems to be good enough.
In addition, at lot of effort is spent explaining to people that NULL doesn't represent 0 for numeric types or an empty string for string types, so it's somewhat counterproductive to treat it as an empty string in this function - bound to confuse people and lead them to think that NULL is after all just a substitute for an empty string or a zero or whatever is appropriate for a particular datatype.
The general idea of null is that it means "it is not known what this value is"; if I replace some characters in a sting with an unknown value, how do I know the value of the resulting string: well, I don't, so the result also ought to say "it is not known what this value is"; at the very least if the function is designed to be counter-intuitive and do something different it ought to be clearly documented, not left for people to find out the hard way.
Tom
December 11, 2012 at 6:48 am
The general idea of null is that it means "it is not known what this value is"; if I replace some characters in a sting with an unknown value, how do I know the value of the resulting string: well, I don't, so the result also ought to say "it is not known what this value is"; at the very least if the function is designed to be counter-intuitive and do something different it ought to be clearly documented, not left for people to find out the hard way.
Well said.
December 11, 2012 at 7:59 am
I think you misunderstand my point regarding the NULL and its referene in BOL.
I said I was in favour of updating BOL to indicate more information about start=0 but I never said that I was not in facour of further elucidation. In-fact, quite the oposite. I think that MS help is generally pretty useless in most areas which is why these forums thrive however, this leads to speculation as we have done hre, as to what MS are doing within the funtions. This is blind leading the blind. It would be far better if MS did the documentation more thoroughly but this may reflect in the price and would we be prepared to pay for it? And what would happen to these intereting quizzes, articles and blog posts if MS did all the leg work?
I would like to see all SQL functions documented fully in BOL with an overview and a subsection or link to a more detailed description an I would like to see this acros all subjects, not just SQL. For example: .net documentation is regularly misleading and overly brief with far too much implied knowledge.
Dave
David Bridge
David Bridge Technology Limited
www.davidbridgetechnology.com
December 11, 2012 at 2:41 pm
L' Eomot Inversé (12/11/2012)
The general idea of null is that it means "it is not known what this value is"; if I replace some characters in a sting with an unknown value, how do I know the value of the resulting string: well, I don't, so the result also ought to say "it is not known what this value is"; at the very least if the function is designed to be counter-intuitive and do something different it ought to be clearly documented, not left for people to find out the hard way.
Aggregate functions such as SUM ignore NULLS completely, so it isn't that counter-intuitive for STUFF to ignore them as well.
However, I think your comment about the documentation needing to be more complete is right on the money.
In fact it would probably make sense for BOL to have a NULL Handling section for every functions help page (just like there is an Example section) if that function can take one as an argument.
Here's hoping.
December 11, 2012 at 2:58 pm
David in .AU (12/11/2012)
L' Eomot Inversé (12/11/2012)
The general idea of null is that it means "it is not known what this value is"; if I replace some characters in a sting with an unknown value, how do I know the value of the resulting string: well, I don't, so the result also ought to say "it is not known what this value is"; at the very least if the function is designed to be counter-intuitive and do something different it ought to be clearly documented, not left for people to find out the hard way.Aggregate functions such as SUM ignore NULLS completely, so it isn't that counter-intuitive for STUFF to ignore them as well.
However, I think your comment about the documentation needing to be more complete is right on the money.
In fact it would probably make sense for BOL to have a NULL Handling section for every functions help page (just like there is an Example section) if that function can take one as an argument.
Here's hoping.
+1 🙂
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply