Solved

CAST() vs TRY_CAST()...

Posted on 2015-02-19
10
650 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:ScottPletcher
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
 
LVL 69

Accepted Solution

by:
ScottPletcher 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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:ScottPletcher
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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

914 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now