STUFF - 2

  • Comments posted to this topic are about the item STUFF - 2

    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]

  • Thank you for another good question Ron!

    NULL value will also be returned in the below two conditions:

    -- When starting position is negative

    SELECT STUFF(@str, -1,5,@stx)

    -- When starting position is greater than the expression length => LEN(@str)

    SELECT STUFF(@str, 7,5,@stx)

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • This was removed by the editor as SPAM

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

  • Simple question to start on monday:)

  • Nice question, thanks.

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

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

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

  • Thanks for the great Monday morning question.

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

  • rhythm.varshney (12/10/2012)


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

    2005 has been off of mainstream support for over a year, not accounting for it anymore seems pretty standard.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • rhythm.varshney (12/10/2012)


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

    +1

  • How STUFF works!

    I hope this helps people who want more explanation about this initially quite odd function

    The 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 statement

    SELECT 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 7

    Note also that the current length of the string is the important factor and not the buffer size first declared, hence

    declare @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 string

    SELECT 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 string

    SELECT 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 null

    SELECT 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')

    Dave

    David Bridge technology Limited

    http://www.davidbridgetechnology.com

    David Bridge
    David Bridge Technology Limited
    www.davidbridgetechnology.com

  • Toreador (12/10/2012)


    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!

    +1 -- and there's no mention in the official documentation of how STUFF treats NULL 'replace with' parameters either.

  • DavidBridgeTechnology.com (12/10/2012)


    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)

    NULL is not nothing!

    If your explanation were correct then the result of

    select '456' + NULL

    would be '456'. And it isn't, it's NULL (which is what you'd expect).

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

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