TRY to CAST

  • Comments posted to this topic are about the item TRY to CAST

  • So TRY_CAST is a new function.  Maybe you should specify the version. πŸ™‚

    5ilverFox
    Consulting DBA / Developer
    South Africa

  • Japie Botma - Tuesday, April 10, 2018 12:59 AM

    So TRY_CAST is a new function.  Maybe you should specify the version. πŸ™‚

    No, it's not a new function!
    From BOL:

    SQL Server (starting with 2012)

  • Very useful function, use it regularly
    thanks,Steve

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    β€œlibera tute vulgaris ex”

  • Never used the function, but didn't need to know anything about it - there's no where clause, no distinct, so must return 5 rows or give an error, and given the function name it was unlikely it would give a casting error

  • Toreador - Tuesday, April 10, 2018 2:22 AM

    Never used the function, but didn't need to know anything about it - there's no where clause, no distinct, so must return 5 rows or give an error, and given the function name it was unlikely it would give a casting error

    Exactly.  The reasoning given for all 5 rows was: 

    "The TRY_CAST will work with all rows and for those that can't be returned, it will return a NULL."

    While it is true that TRY_CAST returns null if the value can't be cast, the reason that there are 5 rows returned is because of the CASE.  If there is no ELSE, CASE will return a null for anything that does not fit one of the WHEN conditions.

  • Quick thought, the return value of the TRY_CAST function is not used in the output of the query, maybe Steve should rewrite the query to support the explanationπŸ˜‰
    😎

  • gvoshol 73146 - Tuesday, April 10, 2018 5:08 AM

    Toreador - Tuesday, April 10, 2018 2:22 AM

    Never used the function, but didn't need to know anything about it - there's no where clause, no distinct, so must return 5 rows or give an error, and given the function name it was unlikely it would give a casting error

    Exactly.  The reasoning given for all 5 rows was: 

    "The TRY_CAST will work with all rows and for those that can't be returned, it will return a NULL."

    While it is true that TRY_CAST returns null if the value can't be cast, the reason that there are 5 rows returned is because of the CASE.  If there is no ELSE, CASE will return a null for anything that does not fit one of the WHEN conditions.

    Not so sure I understand this "the reason that there are 5 rows returned is because of the CASE"  AS I get the same results for both of these queries and wanted to ask why the Case statement ??
    SELECT CASE
        WHEN TRY_CAST(ct.Myval AS INT) IS NOT NULL THEN
         CAST(Myval AS INT)
       END
    FROM #CastTest AS ct;

    SELECT TRY_CAST(ct.Myval AS INT)
    FROM #CastTest AS ct;

  • Budd - Tuesday, April 10, 2018 6:35 AM

    gvoshol 73146 - Tuesday, April 10, 2018 5:08 AM

    Toreador - Tuesday, April 10, 2018 2:22 AM

    Never used the function, but didn't need to know anything about it - there's no where clause, no distinct, so must return 5 rows or give an error, and given the function name it was unlikely it would give a casting error

    Exactly.  The reasoning given for all 5 rows was: 

    "The TRY_CAST will work with all rows and for those that can't be returned, it will return a NULL."

    While it is true that TRY_CAST returns null if the value can't be cast, the reason that there are 5 rows returned is because of the CASE.  If there is no ELSE, CASE will return a null for anything that does not fit one of the WHEN conditions.

    Not so sure I understand this "the reason that there are 5 rows returned is because of the CASE"  AS I get the same results for both of these queries and wanted to ask why the Case statement ??
    SELECT CASE
        WHEN TRY_CAST(ct.Myval AS INT) IS NOT NULL THEN
         CAST(Myval AS INT)
       END
    FROM #CastTest AS ct;

    SELECT TRY_CAST(ct.Myval AS INT)
    FROM #CastTest AS ct;

    Yes, those 2 queries will give the same results.  The second query really should have been the one in the question - that would directly illustrate what TRY_CAST returns.

    Consider this query:
    SELECT CASE
        WHEN ct.Myval = '123' THEN ct.Myval
       END
    FROM #CastTest AS ct

    That will also return 5 lines, 4 of them null.  In this query, it's because the CASE itself returns null.  That's the exact same reason the original query in the question returns 5 lines with some null - because of CASE, not because of TRY_CAST.

  • gvoshol 73146 - Tuesday, April 10, 2018 6:47 AM

    That's the exact same reason the original query in the question returns 5 lines with some null - because of CASE, not because of TRY_CAST.

    The reason the original query returns 5 lines is that there is nothing specified to reduce the number (no WHERE clause, no SELECT DISTINCT, SELECT TOP n, etc). Nothing to do with either CASE or TRY_CAST.

  • gvoshol 73146 - Tuesday, April 10, 2018 6:47 AM

    Budd - Tuesday, April 10, 2018 6:35 AM

    gvoshol 73146 - Tuesday, April 10, 2018 5:08 AM

    Toreador - Tuesday, April 10, 2018 2:22 AM

    Never used the function, but didn't need to know anything about it - there's no where clause, no distinct, so must return 5 rows or give an error, and given the function name it was unlikely it would give a casting error

    Exactly.  The reasoning given for all 5 rows was: 

    "The TRY_CAST will work with all rows and for those that can't be returned, it will return a NULL."

    While it is true that TRY_CAST returns null if the value can't be cast, the reason that there are 5 rows returned is because of the CASE.  If there is no ELSE, CASE will return a null for anything that does not fit one of the WHEN conditions.

    Not so sure I understand this "the reason that there are 5 rows returned is because of the CASE"  AS I get the same results for both of these queries and wanted to ask why the Case statement ??
    SELECT CASE
        WHEN TRY_CAST(ct.Myval AS INT) IS NOT NULL THEN
         CAST(Myval AS INT)
       END
    FROM #CastTest AS ct;

    SELECT TRY_CAST(ct.Myval AS INT)
    FROM #CastTest AS ct;

    Yes, those 2 queries will give the same results.  The second query really should have been the one in the question - that would directly illustrate what TRY_CAST returns.

    Consider this query:
    SELECT CASE
        WHEN ct.Myval = '123' THEN ct.Myval
       END
    FROM #CastTest AS ct

    That will also return 5 lines, 4 of them null.  In this query, it's because the CASE itself returns null.  That's the exact same reason the original query in the question returns 5 lines with some null - because of CASE, not because of TRY_CAST.

    AH, now I understand what you were saying...

  • Toreador - Tuesday, April 10, 2018 9:20 AM

    gvoshol 73146 - Tuesday, April 10, 2018 6:47 AM

    That's the exact same reason the original query in the question returns 5 lines with some null - because of CASE, not because of TRY_CAST.

    The reason the original query returns 5 lines is that there is nothing specified to reduce the number (no WHERE clause, no SELECT DISTINCT, SELECT TOP n, etc). Nothing to do with either CASE or TRY_CAST.

    Granted.

    But the reason you get some NULLS in the 5 lines is because of the CASE, not because of the TRY_CAST.

    This question, beside looking at TRY_CAST, could also be considered to be testing how many lines are returned from a query with a CASE statement in it, when the WHEN's do not cover all possible cases.  That situation could be disastrous for one's desired results when NULLS are not expected and they aren't handled correctly.

  • Explanation updated, points awarded back.

  • The answer query post isn't the same as the first posted question query.  The 2nd post has an 'ELSE' statement which translates the results to -1.  If you run the query in the answer post, you will get -1 instead of nulls.

  • Thanks, corrected the answer. Forgot to change that while fixing the query.

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

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