STUFF - 1

Question

STUFF - 1

Ron McCullough

SSC Guru

Points: 63877
More actions
November 22, 2012 at 8:57 pm

#267172

Comments posted to this topic are about the item STUFF - 1
If everything seems to be going well, you have obviously overlooked something.
Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]
November 23, 2012 at 1:27 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 1

nice 🙂 thank you for the question

(though it was simple and straight question, not sure why i kept occupied myself thinking as it was a trick question... :crazy:)

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

sanket kokane Hall of Fame Points: 3465 More actions · Answer 2

Thanks ,good question.. learn first time about STUFF and then answered it 🙂

-----------------------------------------------------------------------------
संकेत कोकणे

vk-kirov SSCertifiable Points: 7686 More actions · Answer 3

Another question about different stuff 🙂

An interesting example of inserting one string into another. Never thought it could be done that way. I always did it using something like

SELECT LEFT(@string1, 10) + @Stuffing + RIGHT(@string1, LEN(@string1) - 10)

kalyani.k478 Default port Points: 1400 More actions · Answer 4

kalyani.k478

Default port

Points: 1400

November 22, 2012 at 11:07 pm

#1561759

+1

Stuart Davies SSCoach Points: 19505 More actions · Answer 5

Couldn't work out if it was a trick question or not - decided it wasn't

Thanks for the point and the question Ron

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 6

Nice question to end the week. Thanks Ron!

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 7

vk-kirov (11/22/2012)
Another question about different stuff 🙂
An interesting example of inserting one string into another. Never thought it could be done that way. I always did it using something like
SELECT LEFT(@string1, 10) + @Stuffing + RIGHT(@string1, LEN(@string1) - 10)

STUFF is a very powerful function that can be used for many things, but unfortunately is not very well known. I have seen people struggle with RIGHT and REVERSE or SUBSTRING and LENGTH to cut off the first three characters of a string, instead of simply using STUFF(@string, 1, 3, '').

Ron, thanks for this question. I hope it helps spread the knowledge about STUFF.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 8

Thanks for a good question, I know about STUFF for replacing parts of strings but thought the '0' for the length parameter formed part of a trick question (i.e. insert no characters from @stuffing rather than delete no characters from @string1) so selected 16, not 46.

Then after getting it wrong 🙁 and researching the question I found this comment from Zafar Yousafi in BOL:

At first sight it might not be clear directly how to insert a character (or a string) instead of substituting characters in the original string.
To insert a string, without replacing/substituting characters in the original string you need to specify a length of 0 (zero).
SELECT STUFF('abcghi', 4, 0, 'DEF');
-- this results in 'abcDEFghi'.

So, learned something new today - thanks!

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

Toreador SSChampion Points: 11365 More actions · Answer 9

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)

Andre Ranieri SSCrazy Points: 2819 More actions · Answer 10

Nice question. I learned something new about T-SQL today. Thanks!

Andre Ranieri

Revenant SSC-Forever Points: 42467 More actions · Answer 11

Revenant

SSC-Forever

Points: 42467

November 23, 2012 at 10:28 am

#1561901

kalyani.k478 (11/22/2012)
+1

+1 🙂

john.arnott SSChampion Points: 11882 More actions · Answer 12

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.

Ken SSCoach Points: 17191 More actions · Answer 13

Nice question about a function that I do not use enough. Thanks for the question!