Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1717
  • Last Modified:

CAST() vs TRY_CAST()...

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

0
Mike Eghtebas
Asked:
Mike Eghtebas
  • 4
  • 3
1 Solution
 
sdstuberCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Scott PletcherSenior DBACommented:
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
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Thank you for the clarification.
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
I had some sort of mix-up, This is the final result:TRY_CAST2
0
 
Scott PletcherSenior DBACommented:
?? 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
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Bad wording in my part. I had mix up thus the need for the question you clarified.

Thanks,

Mike
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now