April 9, 2018 at 10:59 pm
Comments posted to this topic are about the item TRY to CAST
April 10, 2018 at 12:59 am
So TRY_CAST is a new function. Maybe you should specify the version. 🙂
5ilverFox
Consulting DBA / Developer
South Africa
April 10, 2018 at 1:10 am
Japie Botma - Tuesday, April 10, 2018 12:59 AMSo 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)
April 10, 2018 at 1:20 am
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”
April 10, 2018 at 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
April 10, 2018 at 5:08 am
Toreador - Tuesday, April 10, 2018 2:22 AMNever 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.
April 10, 2018 at 5:55 am
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😉
😎
April 10, 2018 at 6:35 am
gvoshol 73146 - Tuesday, April 10, 2018 5:08 AMToreador - Tuesday, April 10, 2018 2:22 AMNever 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 errorExactly. 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;
April 10, 2018 at 6:47 am
Budd - Tuesday, April 10, 2018 6:35 AMgvoshol 73146 - Tuesday, April 10, 2018 5:08 AMToreador - Tuesday, April 10, 2018 2:22 AMNever 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 errorExactly. 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.
April 10, 2018 at 9:20 am
gvoshol 73146 - Tuesday, April 10, 2018 6:47 AMThat'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.
April 10, 2018 at 9:23 am
gvoshol 73146 - Tuesday, April 10, 2018 6:47 AMBudd - Tuesday, April 10, 2018 6:35 AMgvoshol 73146 - Tuesday, April 10, 2018 5:08 AMToreador - Tuesday, April 10, 2018 2:22 AMNever 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 errorExactly. 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...
April 10, 2018 at 9:35 am
Toreador - Tuesday, April 10, 2018 9:20 AMgvoshol 73146 - Tuesday, April 10, 2018 6:47 AMThat'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.
April 10, 2018 at 9:49 am
Explanation updated, points awarded back.
April 11, 2018 at 7:14 am
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.
April 11, 2018 at 9:03 am
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