Solved

CAST() vs TRY_CAST()...

Posted on 2015-02-19
10
755 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
  • 4
  • 3
10 Comments
 
LVL 73

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 33

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 33

Author Closing Comment

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

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 33

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

777 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