STUFF Function

  • Comments posted to this topic are about the item STUFF Function

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • Euh... wut?

    Is it me and I'm going blind, but aren't the first and the second answer exactly the same?

    The second and the third resultset are just swapped around (and why would you do that?)

    Why put the third resultset before the second resultset in 3 of the 4 answers?

    And the explanation doesn't mention why inserting NULL in another string doesn't result in NULL, but in an empty space instead.

    But thanks for the effort 😀

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

  • Thanks for the question. interesting to know the null is treated differently in the function.

    In regards to answer A and B being the same, I think it's because of copy and paste and forgetting to swap the numbering. I decided to go for the order as it appears and got it right 😀


    Urbis, an urban transformation company

  • Easy question ,

    Srikant

  • Koen Verbeeck (5/16/2011)


    And the explanation doesn't mention why inserting NULL in another string doesn't result in NULL, but in an empty space instead.

    I am curious: bol does not mention the case the last parameter is NULL. Maybe a BUG? 🙂

  • Thanks for the question

    M&M

  • ... made easier by the strange set of answers.

  • Koen Verbeeck (5/16/2011)


    And the explanation doesn't mention why inserting NULL in another string doesn't result in NULL, but in an empty space instead.

    That's what got me.

    Surely it's a bug?

    select 'Vinay, ' + NULL + ', Amit'

    returns NULL, so I'd have thought

    select STUFF('Vinay, Vijay, Amit',8,5,NULL)

    should do the same?

  • Carlo Romagnano (5/17/2011)


    Koen Verbeeck (5/16/2011)


    And the explanation doesn't mention why inserting NULL in another string doesn't result in NULL, but in an empty space instead.

    I am curious: bol does not mention the case the last parameter is NULL. Maybe a BUG? 🙂

    It might be 🙂

    Or just some lazy documentation writers at BOL 😀

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

  • Koen Verbeeck (5/16/2011)


    Euh... wut?

    Is it me and I'm going blind, but aren't the first and the second answer exactly the same?

    The second and the third resultset are just swapped around (and why would you do that?)

    Why put the third resultset before the second resultset in 3 of the 4 answers?

    And the explanation doesn't mention why inserting NULL in another string doesn't result in NULL, but in an empty space instead.

    But thanks for the effort 😀

    My thoughts exactly. I just went for the answers that were in the correct order.

  • Toreador (5/17/2011)


    Koen Verbeeck (5/16/2011)


    And the explanation doesn't mention why inserting NULL in another string doesn't result in NULL, but in an empty space instead.

    That's what got me.

    Surely it's a bug?

    select 'Vinay, ' + NULL + ', Amit'

    returns NULL, so I'd have thought

    select STUFF('Vinay, Vijay, Amit',8,5,NULL)

    should do the same?

    That's exactly my reaction. The BoL page quoted states that the last expression is inserted in the other, and I'm pretty sure that sticking a NULL in the middle of a string should produce NULL. If it doesn't, I would regard it as a bug unless there were a clear statement in BoL that this operation on NULL was unlike any other in SQL (if there is such a statement, I haven't found it). I wonder if it's affected by the CONCAT_NULL_YIELDS_NULL setting?

    Tom

  • Toreador (5/17/2011)


    Koen Verbeeck (5/16/2011)


    And the explanation doesn't mention why inserting NULL in another string doesn't result in NULL, but in an empty space instead.

    That's what got me.

    Surely it's a bug?

    select 'Vinay, ' + NULL + ', Amit'

    returns NULL, so I'd have thought

    select STUFF('Vinay, Vijay, Amit',8,5,NULL)

    should do the same?

    Hi,

    Few days ago, when i was playing with some strings, i found this . That time i also shocked. I feel that we all should know this. that's why i sumbit this question. i think it help us.

    Thx

    Vinay

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • Wow... that didn't work quite the way I'd expected it to! Nice question!

    (And I'm really glad I checked my answer in SQL before actually answering the question!)

    -Ki

  • Danny Ocean (5/17/2011)


    Hi,

    Few days ago, when i was playing with some strings, i found this . That time i also shocked. I feel that we all should know this. that's why i sumbit this question. i think it help us.

    Thx

    Vinay

    That's a very good reason for posting the question. Thanks, Vinay.

    Tom

  • Another ticky tack problem. Sure, I said 2, because the results were the same, just in a different order. It was the last of the two matching result sets. (No order by clause, so order doesn't matter. 🙂 )

    By the way, what setting in SQL Server Management Studio must I have set that required me to put semicolons at the end of each command before they would run without error?

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

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

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