T-SQL COALESCE

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

  • This was removed by the editor as SPAM

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

  • Interesting question, thanks for excellent explanation and comprehensive reference.

  • Good question & explanation, thanx.

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • 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

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

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

  • 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

  • Yup. Got burned by that back in '02.

    Mark
    Just a cog in the wheel.

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

  • 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

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

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

  • Thanks for the question.

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

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