Solved

CAST() vs TRY_CAST()...

Posted on 2015-02-19
10
939 Views
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

0
Comment
Question by:Mike Eghtebas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
10 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 69

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
Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 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 69

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SCOM to SQL port 14xx failed? 1 31
SQL Server Sum Over Multiple Tables 20 33
SQL 2014 missing dll from Bin? 3 33
Need to trim my database size 9 24
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

749 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