T-SQL COALESCE

  • mjagadeeswari

    SSC-Addicted

    Points: 454

    Comments posted to this topic are about the item T-SQL COALESCE

  • This was removed by the editor as SPAM

  • call.copse

    SSCoach

    Points: 16769

    Nice one, I'd just about had enough coffee not to be suckered into the empty string trap.

  • George Vobr

    SSCrazy Eights

    Points: 8934

    Interesting question, thanks for excellent explanation and comprehensive reference.

  • Hany Helmy

    SSChampion

    Points: 13321

    Good question & explanation, thanx.

  • TomThomson

    SSC Guru

    Points: 104763

    call.copse (2/16/2016)


    Nice one, I'd just about had enough coffee not to be suckered into the empty string trap.

    There wasnt any empty string trap, the second argument was ' ' not ''.

    Maybe the second argument was intended to be '' to create such a trap ('' gives the same answer as '') and the space got in by accident.

    Tom

  • DonlSimpson

    SSCertifiable

    Points: 6774

    Interesting to note that:

    SELECT isnull (' ', GETDATE())

    returns a blank string.

    This is one of the reasons I don't like letting sql do implicit conversions for me.

    Don Simpson



    I'm not sure about Heisenberg.

  • Revenant

    SSC-Forever

    Points: 42467

    DonlSimpson (2/16/2016)


    Interesting to note that:

    SELECT isnull (' ', GETDATE())

    returns a blank string.

    This is one of the reasons I don't like letting sql do implicit conversions for me.

    The same. 🙂

    And thanks to Jaga for the coffee break entertainment!

  • TomThomson

    SSC Guru

    Points: 104763

    Revenant (2/16/2016)


    DonlSimpson (2/16/2016)


    Interesting to note that:

    SELECT isnull (' ', GETDATE())

    returns a blank string.

    This is one of the reasons I don't like letting sql do implicit conversions for me.

    The same. 🙂

    And thanks to Jaga for the coffee break entertainment!

    There isn't any implicit conversion required there: ISNULL returns a value of the sanme type as its first parameter, but that doesn't result in any conversion unless the first parameter is NULL.

    select isnull(cast(NULL as varchar(30)), getdate());

    returned 'Feb 16 2016 8:07PM', a string with type varchar(30), a couple of minutes ago (WE Time zone here in Las Canarias).

    I checked the type by using select isnull(cast(NULL as varchar(30)), getdate()) X into silly from tally where I=1 to create a table (silly) and looking at the single column's type.

    I agree that implicit conversion is often undesirable; but sometimes it's quite useful. The implicit conversion done (if neeed) by ISNULL is a lot less confusing that the implicit conversion done by COALESCE because it desn't have the complication of choosing amongst a bunch of types using type precedence.

    And it's a nice little question, that requires one to know the precedence rule.

    Tom

  • starunit

    SSCommitted

    Points: 1807

    Yup. Got burned by that back in '02.

    Mark
    Just a cog in the wheel.

  • twin.devil

    SSC-Insane

    Points: 22208

    Nice question. A very good reminder. Thanks for sharing.

  • mjagadeeswari

    SSC-Addicted

    Points: 454

    TomThomson (2/16/2016)


    call.copse (2/16/2016)


    Nice one, I'd just about had enough coffee not to be suckered into the empty string trap.

    There wasnt any empty string trap, the second argument was ' ' not ''.

    Maybe the second argument was intended to be '' to create such a trap ('' gives the same answer as '') and the space got in by accident.

    Had checked earlier before posting the ques what is the difference ..string with or without space? got same result on conversion, and i decided to go for string with a single space in the ques. Hehe. Trap.:-P

  • mjagadeeswari

    SSC-Addicted

    Points: 454

    Revenant (2/16/2016)


    DonlSimpson (2/16/2016)


    Interesting to note that:

    SELECT isnull (' ', GETDATE())

    returns a blank string.

    This is one of the reasons I don't like letting sql do implicit conversions for me.

    The same. 🙂

    And thanks to Jaga for the coffee break entertainment!

    :satisfied:

  • mjagadeeswari

    SSC-Addicted

    Points: 454

    TomThomson (2/16/2016)


    Revenant (2/16/2016)


    DonlSimpson (2/16/2016)


    Interesting to note that:

    SELECT isnull (' ', GETDATE())

    returns a blank string.

    This is one of the reasons I don't like letting sql do implicit conversions for me.

    The same. 🙂

    And thanks to Jaga for the coffee break entertainment!

    There isn't any implicit conversion required there: ISNULL returns a value of the sanme type as its first parameter, but that doesn't result in any conversion unless the first parameter is NULL.

    select isnull(cast(NULL as varchar(30)), getdate());

    returned 'Feb 16 2016 8:07PM', a string with type varchar(30), a couple of minutes ago (WE Time zone here in Las Canarias).

    I checked the type by using select isnull(cast(NULL as varchar(30)), getdate()) X into silly from tally where I=1 to create a table (silly) and looking at the single column's type.

    I agree that implicit conversion is often undesirable; but sometimes it's quite useful. The implicit conversion done (if neeed) by ISNULL is a lot less confusing that the implicit conversion done by COALESCE because it desn't have the complication of choosing amongst a bunch of types using type precedence.

    And it's a nice little question, that requires one to know the precedence rule.

    Noted Tom. Thank you so much. yes, main thing is to know precedence rule here.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the question.

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

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