Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

STUFF - 1 Expand / Collapse
Author
Message
Posted Friday, November 23, 2012 2:10 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:55 AM
Points: 1,813, Visits: 6,586
There's an explanation of sorts here
http://msdn.microsoft.com/en-us/library/ms188043(SQL.105).aspx
(in the comments section at the end)
Post #1388081
Posted Friday, November 23, 2012 8:46 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 11:15 AM
Points: 292, Visits: 319
Nice question. I learned something new about T-SQL today. Thanks!

Andre Ranieri
Post #1388188
Posted Friday, November 23, 2012 10:28 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, October 24, 2014 12:43 PM
Points: 4,126, Visits: 3,428
kalyani.k478 (11/22/2012)
+1

+1
Post #1388211
Posted Friday, November 23, 2012 11:04 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,388, Visits: 3,039
First, thank you for prodding me to read about STUFF, a function I'd seen mentioned but which I haven't used.

Your explanation of answer starts with
I could not find an explanation of the "lengthing" of the declared item....

This was the point I considered after reading the BOL entry and before choosing my answer. The given QOD code does not assign the modified value to either of the variables. The function may return data with a type inherited from the input, but the length of the function's output is not constrained by the lenghth of either input string. Consider what you may naturally expect if you were to add these lines to the end of the given script:
DECLARE @string2 VARCHAR(50)

set @string2 = STUFF(@string1, 11,0,@Stuffing)
select @string2
,len(@string2)

A truncated value of "Microsoft ******" or "Microsoft ********************" would hardly be acceptable.
Post #1388218
Posted Friday, November 23, 2012 12:54 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 12:42 PM
Points: 3,388, Visits: 2,021
Nice question about a function that I do not use enough. Thanks for the question!
Post #1388242
Posted Saturday, November 24, 2012 2:39 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, November 11, 2013 2:35 PM
Points: 673, Visits: 123
Nice Stuff.

T-SQL is Great
Post #1388292
Posted Saturday, November 24, 2012 2:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:51 AM
Points: 7,874, Visits: 9,612
Nice question - and a reminder to me to use STUFF more often.

I don't understand why the explanation worries about the result length. . I agree with John Arnott's comments above about this. String functions either do or don't have a length parameter, and the ones that don't have a resulting length that can be any valid length (except MAX, unless a parameter of the function is declared with length MAX). Why should anyone expect STUFF to behave differently from + in this respect?


Tom
Post #1388293
Posted Monday, November 26, 2012 12:41 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:12 AM
Points: 1,953, Visits: 2,397
nice question for the week..
+1
learn about STUFF today



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1388452
Posted Monday, November 26, 2012 5:37 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 12, 2014 4:19 AM
Points: 701, Visits: 1,145
Thanks for the STUFF question.
Post #1388559
Posted Monday, November 26, 2012 10:47 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, November 20, 2014 9:23 AM
Points: 615, Visits: 690
Nice question and I learned STUFF.

Aigle de Guerre!
Post #1388726
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse