SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by bitbucket / STUFF - 2 / Latest Posts

RE: STUFF - 2

manik123 — Wed, 23 Jan 2013 08:08:47 GMT

nice one... i thougt the string length is started from 0. it also give any replace string... :-)

RE: STUFF - 2

udayroy15 — Thu, 13 Dec 2012 05:52:23 GMT

require a good concentration to find out the exact result :)

RE: STUFF - 2

DavidBridgeTechnology.com — Wed, 12 Dec 2012 07:28:18 GMT

[quote]it would probably make sense for BOL to have a NULL Handling section for every functions help page [/quote]Great idea!Dave

RE: STUFF - 2

Michael Poppers — Tue, 11 Dec 2012 14:58:08 GMT

[quote][b]David in .AU (12/11/2012)[/b][hr][quote][b]L' Eomot Inversé (12/11/2012)[/b][hr]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.[/quote]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.[/quote]+1 :-)

RE: STUFF - 2

David in .AU — Tue, 11 Dec 2012 14:41:47 GMT

[quote][b]L' Eomot Inversé (12/11/2012)[/b][hr]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.[/quote]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.

RE: STUFF - 2

DavidBridgeTechnology.com — Tue, 11 Dec 2012 07:59:48 GMT

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

RE: STUFF - 2

Del Lee — Tue, 11 Dec 2012 06:48:12 GMT

[quote]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.[/quote]Well said.

RE: STUFF - 2

L' Eomot Inversé — Tue, 11 Dec 2012 05:29:15 GMT

[quote][b]DavidBridgeTechnology.com (12/11/2012)[/b][hr]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.[/quote]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.

RE: STUFF - 2

DavidBridgeTechnology.com — Tue, 11 Dec 2012 04:34:48 GMT

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.

RE: STUFF - 2

David in .AU — Mon, 10 Dec 2012 19:03:57 GMT

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 = NULLHowever, 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...

RE: STUFF - 2

john.arnott — Mon, 10 Dec 2012 18:26:47 GMT

[quote][b]rhythm.varshney (12/10/2012)[/b][hr]Nice question.However it will fetch error in SQL 2005 not sure about upper version as we can not assign value while declaration.[/quote]One could bypass this speed-bump with an explicit assignment:[code="sql"]DECLARE @str VARCHAR(6)Set @str = 'ServeR'DECLARE @stx VARCHAR(4)[/code]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: [url]http://msdn.microsoft.com/en-us/library/ms188043(v=sql.90).aspx[/url]SQL 2008: [url]http://msdn.microsoft.com/en-us/library/ms188043(v=sql.100).aspx[/url]SQL 2008 R2: [url]http://msdn.microsoft.com/en-us/library/ms188043(v=sql.105).aspx[/url]

RE: STUFF - 2

Del Lee — Mon, 10 Dec 2012 14:44:55 GMT

I agree with Toreador.

RE: STUFF - 2

asifkareem — Mon, 10 Dec 2012 13:58:50 GMT

Good One!

RE: STUFF - 2

Revenant — Mon, 10 Dec 2012 13:12:45 GMT

Ron's QotDs are always interesting. Thanks, Ron!

RE: STUFF - 2

SQLRNNR — Mon, 10 Dec 2012 11:21:40 GMT

Thanks Ron

RE: STUFF - 2

Meow Now — Mon, 10 Dec 2012 08:35:43 GMT

[quote]NULL is [i]not [/i]nothing!If your explanation were correct then the result ofselect '456' + NULLwould be '456'. And it isn't, it's NULL (which is what you'd expect).[/quote]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.

RE: STUFF - 2

L' Eomot Inversé — Mon, 10 Dec 2012 08:34:34 GMT

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:[quote][b]Toreador (12/10/2012)[/b][hr]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![/quote]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).

RE: STUFF - 2

sknox — Mon, 10 Dec 2012 08:26:46 GMT

[quote][b]Toreador (12/10/2012)[/b][hr][quote][b]DavidBridgeTechnology.com (12/10/2012)[/b][hr]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)[/quote]NULL is [i]not [/i]nothing!If your explanation were correct then the result ofselect '456' + NULLwould be '456'. And it isn't, it's NULL (which is what you'd expect).[/quote]Actually, nothing is perhaps the best characterization of NULL we have. But the mistake is that [i]nothing[/i] is not the same thing as [i]an empty string[/i]. 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.

RE: STUFF - 2

Toreador — Mon, 10 Dec 2012 08:00:17 GMT

[quote][b]DavidBridgeTechnology.com (12/10/2012)[/b][hr]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)[/quote]NULL is [i]not [/i]nothing!If your explanation were correct then the result ofselect '456' + NULLwould be '456'. And it isn't, it's NULL (which is what you'd expect).

RE: STUFF - 2

sknox — Mon, 10 Dec 2012 07:53:50 GMT

[quote][b]Toreador (12/10/2012)[/b][hr]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![/quote]+1 -- and there's no mention in the official documentation of how STUFF treats NULL 'replace with' parameters either.

RE: STUFF - 2

DavidBridgeTechnology.com — Mon, 10 Dec 2012 07:52:42 GMT

How STUFF works!I hope this helps people who want more explanation about this initially quite odd functionThe whole of the RIGHT string will be INJECTED in to the LEFT string at the START position.The LENGTH parameter indicates the number of characters TO OVERWRITE (delete before insertion) from the FIRST string starting at the character indicated by the START parameter.(The START and LENGTH parameters take no interest in the RIGHT string)IMPORTANT: The start position must be a character position in the FIRST string and as 0 is not a valid character position anything with a START of 0 will result in NULL to indicate failure.NOTE ALSO: There is no way to append to the first string for the same reason. In the example the string length is 6 so 7 is not a valid character position in the left string.This can be demonstrated well with the following SELECT STUFF('123456', 0,3,'XXXXXX') -- NULL - What were you expecting? Looking up character 0 in the left string returned nothing so SQL does not know what to do with the statementSELECT STUFF('123456', 1,3,'XXXXXX') -- First three chars deleted, then injection then remaining part of the left string "XXXXXX456"SELECT STUFF('123456', 2,3,'XXXXXX') -- Returns the left string upto the start position then deletes next three chars, new string inserted , then remainder of left string "1XXXXXX56" SELECT STUFF('123456', 3,3,'XXXXXX') -- Returns the left string upto the start position then deletes next three chars, new string inserted , then remainder of left string "12XXXXXX6" SELECT STUFF('123456', 4,3,'XXXXXX') -- "123XXXXXX"SELECT STUFF('123456', 5,3,'XXXXXX') -- "1234XXXXXX"SELECT STUFF('123456', 6,3,'XXXXXX') -- "12345XXXXXX"SELECT STUFF('123456', 7,3,'XXXXXX') -- Were you expecting "123456XXXXXX"? - Cannot insert at position 7 because left string does not have position 7Note also that the current length of the string is the important factor and not the buffer size first declared, hencedeclare @a varchar(8) = '12345678' SET @a = '123456'SELECT STUFF(@a, 7,3,'XXXXXX') still returns NULL (because the string size is now 6 - not 8 chars)So what happens if we put a NULL into the string?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)SELECT STUFF('123456', 2,3,NULL) -- "156" - SQL has found the second character, removed it and two next to it (3 in total) and inserted nothing into the stringSELECT STUFF('123456', 3,3,NULL) -- SELECT STUFF('123456', 4,3,NULL) -- "123" - SQL has found the fourth character, removed it and two next to it (3 in total) which is actually all of the left string and then inserted nothing into the stringSELECT STUFF('123456', 5,3,NULL) -- SELECT STUFF('123456', 6,3,NULL) -- So for this expression where @str is 'ServeR' which is a 6 character string and @stx is nullSELECT STUFF(@str, 1,6,@stx) AS 'S-1' SQL has found the first character 'S' and deleted it and the next 5 characters (6 in total) and then inserted nothing in to the string, leaving it as an empty string. Remeber we cannot insert NULL into the string - inserting null just does nothing to the string that was there before (which is now empty, i.e. zero length string)and for SELECT STUFF(@str, 0,5,@stx) AS 'S-2'the contents of @str is irrelavant as it will not have a 0 position. S is the first char at position 1. The contect of @stx is also irrelavent as we are not going to return anything anyway.All ofthese return NULL just because start is 0 ...SELECT STUFF('123456', 0,5,NULL)SELECT STUFF(NULL, 0,5,NULL)SELECT STUFF('123456', 0,5,'XYZ')DaveDavid Bridge technology Limitedwww.davidbridgetechnology.com

RE: STUFF - 2

mbova407 — Mon, 10 Dec 2012 07:45:52 GMT

[quote][b]rhythm.varshney (12/10/2012)[/b][hr]Nice question.However it will fetch error in SQL 2005 not sure about upper version as we can not assign value while declaration.[/quote]+1

RE: STUFF - 2

mtassin — Mon, 10 Dec 2012 07:32:28 GMT

RE: STUFF - 2

Toreador — Mon, 10 Dec 2012 04:56:31 GMT

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!

RE: STUFF - 2

(Bob Brown) — Mon, 10 Dec 2012 04:21:01 GMT

Thanks for the great Monday morning question.

RE: STUFF - 2

sipas — Mon, 10 Dec 2012 04:07:26 GMT

More STUFFing - it must be nearly Christmas. Thanks for the question.

RE: STUFF - 2

rhythm.varshney — Mon, 10 Dec 2012 03:17:11 GMT

Nice question.However it will fetch error in SQL 2005 not sure about upper version as we can not assign value while declaration.

RE: STUFF - 2

Koen Verbeeck — Mon, 10 Dec 2012 02:38:06 GMT

Nice question, thanks.

RE: STUFF - 2

kalyani.k478 — Mon, 10 Dec 2012 02:00:53 GMT

Simple question to start on monday:)

RE: STUFF - 2

paul s-306273 — Mon, 10 Dec 2012 01:42:27 GMT

Guess you have to know your stuff to get today's QOTD right...

RE: STUFF - 2

Stewart "Arturius" Campbell — Mon, 10 Dec 2012 00:21:09 GMT

Interesting question, Thanks, Ron

RE: STUFF - 2

Lokesh Vij — Mon, 10 Dec 2012 00:11:07 GMT

Thank you for another good question Ron!NULL value will also be returned in the below two conditions:[code="sql"]-- When starting position is negativeSELECT STUFF(@str, -1,5,@stx)-- When starting position is greater than the expression length => LEN(@str)SELECT STUFF(@str, 7,5,@stx)[/code]

STUFF - 2

bitbucket-25253 — Sat, 08 Dec 2012 11:15:32 GMT

Comments posted to this topic are about the item [B]STUFF - 2[/B]