May 17, 2011 at 8:10 am
Nice question. Easy answer for me, but I have used string functions a lot.
I understand all the comments about a BUG, or BOL not getting it right. The BOL are not very clear as always.
This function does work like most that support both character and Binary inputs and guarantee a certain return value.
The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
Later it also mentions:
If the start position or the length is negative, or if the starting position is larger than length of the first string, a null string is returned. If the length to delete is longer than the first string, it is deleted to the first character in the first string.
Now why the BOL author decided to use the words null string instead of empty string I will never know. Because that would make it easier to understand.
When the first character_expression is not a NULL then the Function guarantee us at least an empty zero length character_expression is returned. So when the second is NULL, it gets converted to a null string or empty zero length string so at least a zero length representation of the first character_expression is returned.
I wonder if anyone can post about how STUFF works with Binary values?
I bet we get the same results.
May 17, 2011 at 8:29 am
Jaroslav Pfauser (5/17/2011)
Damn it.I answered correctly, but there was 2 same answers and I choose the wrong one
Or is there some difference between those 2 answers?
1. Vinay, Anup, Amit 2. Vinay, , Amit 3. NULL
1. Vinay, Anup, Amit 3. NULL 2. Vinay, , Amit
:angry:
Only order is changed, just for took few minutes for think about it.
Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
May 17, 2011 at 8:33 am
Steve Jones - SSC Editor (5/17/2011)
tabraham 36699 (5/17/2011)
Another ticky tack problem. Sure, I said 2, because the results were the same, just in a different order. It was the last of the two matching result sets. (No order by clause, so order doesn't matter. 🙂 )By the way, what setting in SQL Server Management Studio must I have set that required me to put semicolons at the end of each command before they would run without error?
Ordering does matter because these are 3 select statements, not one with 3 results. The order comes back consistently because there are separate statements in a batch.
Hi, i knew that ordering is doesn't matter. but i did just user take some time to think about it..
thx
Vinay
Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
May 17, 2011 at 9:01 am
thanks for the question
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
May 17, 2011 at 9:01 am
SanDroid (5/17/2011)
Nice question. Easy answer for me, but I have used string functions a lot.I understand all the comments about a BUG, or BOL not getting it right. The BOL are not very clear as always.
This function does work like most that support both character and Binary inputs and guarantee a certain return value.
The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
Later it also mentions:
If the start position or the length is negative, or if the starting position is larger than length of the first string, a null string is returned. If the length to delete is longer than the first string, it is deleted to the first character in the first string.
Now why the BOL author decided to use the words null string instead of empty string I will never know. Because that would make it easier to understand.
When the first character_expression is not a NULL then the Function guarantee us at least an empty zero length character_expression is returned. So when the second is NULL, it gets converted to a null string or empty zero length string so at least a zero length representation of the first character_expression is returned.
I wonder if anyone can post about how STUFF works with Binary values?
I bet we get the same results.
The problem with this reasoning is that the guarantee isn't really correct. Or at least it should not be (the quarrel would be with MS, not with SanDroid). I mean, the minute you introduce NULL, all bets should be off. There is no way of knowing whether NULL would be an empty string, and to automatically substitute that is to communicate a message that is not appropriate -- NULL should never be thought of as equal to an empty string because it is not remotely the same thing.
Again, not aimed at you SanDroid. more at MS.
May 17, 2011 at 12:26 pm
voted +1
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 17, 2011 at 12:40 pm
Koen Verbeeck (5/17/2011)
voted +1
What would you recomend they change the behavior to?
The function would still need to return at least a zero length character_expression if the first character_expression is not NULL.
STUFF has worked this way for a while, what would a change to this behavior do to existing code?
I would really not want to be the one to pay for an update of the code that expects STUFF to work like this in my environment. :w00t:
Just the code in my environment might keep me busy for a year so testing and correcting so that it still always gets the first character_expresion with the expect text deleted in the output even when the second expression is a NULL value. 😎
May 17, 2011 at 1:27 pm
jeff.mason (5/17/2011)
Burned by what SHOULD be versus what IS.Logically, NULL plus anything should be NULL. Period, end of story. NULL means "unknown" and you can't assume that you can replace NULL with a blank string and have it be right. It should be NULL and NULL on 2 and 3. The fact that it is not is either a bug or else needs a clear BOL discussion.
That's why I got it wrong, too. I assumed that NULL would yield NULL. Very strange. Looks like an easy way to make a quick buck off my SQL buddies 😀 (silver lining and all that...)
Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
May 17, 2011 at 1:47 pm
I spent more time looking at it.
SELECT STUFF('Vinay, Vijay, Amit',8,5,NULL)
is identical result wise to
SELECT STUFF('Vinay, Vijay, Amit',8,5,'') (those are single quotes)
I would never have expected that. I would think to replace a string with no value would require 2 single quotes, not a NULL.
SELECT STUFF('Vinay, Vijay, Amit',1,999999999,NULL)
does not create an error until you have enough digits to get an arithmetic overflow. It just goes to the end of the string or the number of digits specified, whichever come first.
And in response to some earlier comment
SET CONCAT_NULL_YIELDS_NULL {ON | OFF}
has no effect.
Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
May 17, 2011 at 3:52 pm
Got it wrong for the "right" reason. I saw that the second and third example contained NULL valued parameters, and immediately picked the only answer with NULL as result for number two and three.
I agree with the rest of you that this must be a bug.
May 18, 2011 at 3:30 am
Another bug vote here, and another wrong answer for me.
NULL is NULL...fullstop. You never know what NULL is so regardless if you insert, concatenate, stick it somewhere in the string: the whole string is (SHOULD BE!) now unknown.
My guess is anyone who got this right either ran the commands first, read the question wrong, of has bumped into this before.
Carlton.
May 18, 2011 at 6:57 am
Carlton Leach (5/18/2011)
Another bug vote here, and another wrong answer for me.NULL is NULL...fullstop. You never know what NULL is so regardless if you insert, concatenate, stick it somewhere in the string: the whole string is (SHOULD BE!) now unknown.
My guess is anyone who got this right either ran the commands first, read the question wrong, of has bumped into this before.
Carlton.
Or has used STUFF for the 11+ years it has worked this way... :hehe:
http://www.mssqltips.com/tip.asp?tip=1026
I can say the documentation used to be better.
May 18, 2011 at 7:11 am
SanDroid (5/18/2011)
Carlton Leach (5/18/2011)
Another bug vote here, and another wrong answer for me.NULL is NULL...fullstop. You never know what NULL is so regardless if you insert, concatenate, stick it somewhere in the string: the whole string is (SHOULD BE!) now unknown.
My guess is anyone who got this right either ran the commands first, read the question wrong, of has bumped into this before.
Carlton.
Or has used STUFF for the 11+ years it has worked this way... :hehe:
http://www.mssqltips.com/tip.asp?tip=1026
I can say the documentation used to be better.
That link doesn't explain why MS let STUFF ever work this way. If it has always been like this it has always been wrong. At the very least it should tie into the override settings as mentioned above. I see your point about changing it after 11 years, but there is no reason it should ever have been this way to begin with. If you want that sort of functionality, embed the second string in a ISNULL call to replace NULL with an empty string. That's easy, takes no cycles from the process, and is 100% reliable. MS wasn't very consistent in how they treated NULL in SQL Server unfortunately.
May 18, 2011 at 7:45 am
jeff.mason (5/18/2011)
If you want that sort of functionality, embed the second string in a ISNULL call to replace NULL with an empty string. That's easy, takes no cycles from the process, and is 100% reliable. MS wasn't very consistent in how they treated NULL in SQL Server unfortunately.
ISNULL(a,b) was not always there.
STUFF was an added STRING function that comes from C.
MS did not do this... SYBASE did.
Yep MS SQL server as you know it started as a limited re-labled version of SYBASE sql server created for MS.
Then MS decided to have someone other than Sybase work on it.
Also funny enough SYBASE still has (and ORACLE) good documentation on how this works the way it does.
I guess all this is easier if you lived it. 😎
Viewing 15 posts - 31 through 45 (of 64 total)
You must be logged in to reply to this topic. Login to reply