Improve company productivity with a Business Account.Sign Up

x
?
Solved

CAST() vs TRY_CAST()...

Posted on 2015-02-19
10
Medium Priority
?
2,001 Views
1 Endorsement
Last Modified: 2015-02-21
The text included below explains the use of Try_Cast and when it will be required. But, as shown in the image, they function the same. The text from the book seems to suggest the codes in the middle column (labeled Incorrect) shouldn't work.

Question: How can I change my sample code to see clearly the difference between Cast and Try_Cast?
Try_Cast

Code:
CREATE TABLE #t 
(
   propertytype VARCHAR(20), 
   propertyval VARCHAR(20)
)
GO
INSERT #t(propertytype, propertyval) VALUES
   ('INT', '5')
   , ('INT', '15' )
   , ('STRING', 'ABC')
   , ('DATE', '2/20/2015')
GO
SELECT * FROM #t
WHERE propertytype = 'INT' AND TRY_CAST(propertyval AS INT) > 10
GO
SELECT * FROM #t
WHERE propertytype = 'INT' AND CAST(propertyval AS INT) > 10
GO
SELECT * FROM #t
WHERE propertytype = 'DATE' AND CAST(propertyval AS DATETIME) = '2/20/2015'
GO
SELECT * FROM #t
WHERE propertytype = 'DATE' AND TRY_CAST(propertyval AS DATETIME) = '2/20/2015'
GO
SELECT * FROM #t
WHERE propertytype = 'STRING' AND CAST(propertyval AS varchar(10)) = 'ABC'
GO
SELECT * FROM #t
WHERE propertytype = 'STRING' AND TRY_CAST(propertyval AS varchar(10)) = 'ABC'

Open in new window


Text from the book I am reading:
For example, consider the following filter predicate.
WHERE propertytype = 'INT' AND CAST(propertyval AS INT) > 10
Suppose that the table being queried holds different property values. The propertytype
column represents the type of the property (an INT, a DATE, and so on), and the propertyval
column holds the value in a character string. When propertytype is 'INT', the value in
propertyval is convertible to INT; otherwise, not necessarily.
Some assume that unless precedence rules dictate otherwise, predicates will be evaluated
from left to right, and that short circuiting will take place when possible. In other words, if the
first predicate propertytype = 'INT' evaluates to false, SQL Server won’t evaluate the second
predicate CAST(propertyval AS INT) > 10 because the result is already known. Based on this
assumption, the expectation is that the query should never fail trying to convert something
that isn’t convertible.
The reality, though, is different. SQL Server does internally support a short-circuit concept;
however, due to the all-at-once concept in the language, it is not necessarily going to
evaluate the expressions in left-to-right order. It could decide, based on cost-related reasons,
to start with the second expression, and then if the second expression evaluates to true,
to evaluate the first expression as well. This means that if there are rows in the table where
propertytype is different than 'INT', and in those rows propertyval isn’t convertible to INT, the
query can fail due to a conversion error.
68 Chapter 3 Filtering and Sorting Data
You can deal with this problem in a number of ways. A simple option is to use the TRY_CAST
function instead of CAST. When the input expression isn’t convertible to the target type,
TRY_CAST returns a NULL instead of failing. And comparing a NULL to anything yields unknown.
Eventually, you will get the correct result, without allowing the query to fail. So your
WHERE clause should be revised like the following.
WHERE propertytype = 'INT' AND TRY_CAST(propertyval AS INT) > 10

Open in new window

1
Comment
Question by:Mike Eghtebas
  • 4
  • 3
8 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 40619849
Since the question originally include the Oracle topic area I'll go ahead and answer for that platform.


Oracle doesn't support the TRY_CAST() function.


Oracle does allow for CAST() though


for example...


select sysdate,systimestamp,cast(sysdate as timestamp), cast(systimestamp as date)  from dual;
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40619893
Did you read the text from the book?  It explains exactly why the "Incorrect" method is, in fact, incorrect.

To see it more clearly, add a "INT" value in the table that is not actually an integer value.
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40619919
Hi Scott,

Re:> It explains exactly why the "Incorrect" method is, in fact, incorrect.

If it is incorrect, then why it gives the same output as the column labeled "Correct"?

re:> To see it more clearly, add a "INT" value in the table that is not actually an integer value.

Per the setup in the book, I am supposed to keep this field as varchar with values like '5', '10', etc.

Mike
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 40619924
To see it more clearly, add a "INT" value in the table that is not actually an integer value.

INSERT #t(propertytype, propertyval) VALUES
   ('INT', 'ABC' )

Then run the queries with:
WHERE propertytype = 'INT'
again.
0
 
LVL 34

Author Closing Comment

by:Mike Eghtebas
ID: 40619930
Thank you for the clarification.
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40619961
I had some sort of mix-up, This is the final result:TRY_CAST2
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40619982
?? What is the "mix up" ??  The entire point of the "Correct" method is that will be able to run successfully even if the value in the column is not a valid integer value.
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40620036
Bad wording in my part. I had mix up thus the need for the question you clarified.

Thanks,

Mike
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

608 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question