STUFF - 1

  • 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]

  • 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.

  • Thanks ,good question.. learn first time about STUFF and then answered it ๐Ÿ™‚

    -----------------------------------------------------------------------------
    เคธเค‚เค•เฅ‡เคค เค•เฅ‹เค•เคฃเฅ‡

  • 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)

  • +1

  • 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

  • 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

  • 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/

  • This was removed by the editor as SPAM

  • 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.

  • 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)

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

    Andre Ranieri

  • kalyani.k478 (11/22/2012)


    +1

    +1 ๐Ÿ™‚

  • 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.

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

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

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