COALESCE

  • PChiragS

    SSCarpal Tunnel

    Points: 4965

    Nice question..Thanks.

  • SQLRNNR

    SSC Guru

    Points: 281243

    Andrew Watson-478275 (9/20/2013)


    As well as the reference given, this one should be included too:

    http://msdn.microsoft.com/en-us/library/ms190309(v=sql.105).aspx

    +1

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • NBSteve

    Hall of Fame

    Points: 3427

    Good question, but it does miss out on a good opportunity to point out that these two have the same result (in this particular case):

    SELECT COALESCE(Col1, Col2) AS 'FirstNotNull' FROM dbo.T_TEST

    SELECT ISNULL(Col1, Col2) AS 'FirstNotNull' FROM dbo.T_TEST

    while these two do not:

    SELECT COALESCE(Col2, Col1) AS 'FirstNotNull' FROM dbo.T_TEST

    SELECT ISNULL(Col2, Col1) AS 'FirstNotNull' FROM dbo.T_TEST

    (The reason being that COALESCE uses data type precedence rules, converting results to DECIMAL, while ISNULL uses the data type of the first argument, assuming it's not a literal NULL, converting everything to INT in the latter example.)

  • Revenant

    SSC-Forever

    Points: 42467

    Nice and easy - thanks!

  • jfgoude

    SSCrazy

    Points: 2586

    Elementary, my dear Watson

    +1

    Thanks

Viewing 5 posts - 16 through 20 (of 20 total)

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