Post reply

COALESCE

  • September 20, 2013 at 7:53 am

    #1651932

    Nice question..Thanks.

  • September 20, 2013 at 10:31 am

    #1651982

    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

  • September 20, 2013 at 11:38 am

    #1651991

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

  • September 20, 2013 at 11:39 am

    #1651992

    Nice and easy - thanks!

  • October 10, 2013 at 7:38 am

    #1657543

    Elementary, my dear Watson

    +1

    Thanks

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

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