TRY_CONVERT( ) returning a NULL vs. returning an error
-
I am testing the TRY_CONVERT( ). Here is my code:
SELECT TRY_CONVERT(int, 'ABC') as Result;-- returns a NULL
SELECT TRY_CONVERT (XML, 10) as Result;-- results in an errorThe conversion fails in the 2nd example, so a NULL is returned. However, the conversion also fails in the 3rd example, but instead of returning a NULL, an error results.
What makes the 2nd example different than the 1st example? How can tell by looking at a TRY_CONVERT( ) example, if a NULL would be returned or if an error would result?
-
My understanding (experts correct me if I am wrong) is that if the conversion is potentially possible, but the conversion is invalid, then you get NULL. If the conversion is going to be impossible, you get an ERROR.
In your examples, it is POSSIBLE to convert a VARCHAR (or CHAR) to an INT ('10' for example) but depending on the input, it may not work ('ABC' for example). If it is POSSIBLE and the input is valid, it converts, otherwise it gives NULL.
In your example, it is IMPOSSIBLE to convert an INT to XML. There is no example INT that you can use to convert to XML. They are incompatible data types. Here it gives an error.
Something to note, those are NOT the only possible unexpected outputs. A third example - try using TRY_CONVERT to convert a 2 digit INT to CHAR(1) or VARCHAR(1). If the conversion is possible, but string truncation will occur, you get * instead.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
Brian's explanation is correct.
For documentation, see this link, which states:
TRY_CONVERT takes the value passed to it and tries to convert it to the specified data_type. If the cast succeeds, TRY_CONVERT returns the value as the specified data_type; if an error occurs, null is returned. However if you request a conversion that is explicitly not permitted, then TRY_CONVERT fails with an error.
There is a graphic at this link that shows which data type combinations are disallowed for conversion altogether. INT to XML is indeed one of the explicitly disallowed conversions.
Cheers!
- michael.leach2015 wrote:
I am testing the TRY_CONVERT( ). Here is my code:
SELECT TRY_CONVERT(int, 'ABC') as Result;-- returns a NULL
SELECT TRY_CONVERT (XML, 10) as Result;-- results in an errorThe conversion fails in the 2nd example, so a NULL is returned. However, the conversion also fails in the 3rd example, but instead of returning a NULL, an error results.
What makes the 2nd example different than the 1st example? How can tell by looking at a TRY_CONVERT( ) example, if a NULL would be returned or if an error would result?
I know you're trying to learn things and that's really good. But... with the idea of teaching a man to fish, your question is an indication that you're falling into the same trap as a whole lot of other people and, having been following your questions a bit because you seem to be trying to do well, I'm going to snap you with the proverbial towel to get your attention.
Don't be so lazy... the first thing you should do when you run into something like this is seek out the Microsoft Documentation. Us old timers refer to it as "BOL" (Books Online) because that's what it used to be called and it used to come on the same CDs that you installed SQL Server with. You can still download it but you don't actually have to... it's all available online in what are now referred to as the Microsoft Docs.
To answer your own question and possibly learn more than just the answer to your question, Yabingooducklehoo is your friend. Just do a search for "try_convert in SQL Server". Usually, the official documentation from Microsoft (which I still refer to as "BOL" but will have a label on it of Microsoft Docs) will be returned as the first choice. Here's the link it came up with as the first choice...
https://docs.microsoft.com/en-us/sql/t-sql/functions/try-convert-transact-sql?view=sql-server-ver15
In the article on that link, it clearly states the answer to your question (as well as samples that demonstrate your exact question, which I've not posted here)... I've underlined the answer it provides to your question.
Remarks
TRY_CONVERT takes the value passed to it and tries to convert it to the specified data_type. If the cast succeeds, TRY_CONVERT returns the value as the specified data_type; if an error occurs, null is returned. However if you request a conversion that is explicitly not permitted, then TRY_CONVERT fails with an error.
THAT, however, should rile up your intellectual curiosity a bit! You're next questions should be...
1. What is the full syntax for the function?
That, of course, is answered by the following at the top of the same article.
Syntax
TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )
2. You next question should be "Hmmmm... what is that "style" argument?", which is provided as well...
Arguments
data_type [ ( length ) ]
The data type into which to cast expression.
expression
The value to be cast.
style
Optional integer expression that specifies how the TRY_CONVERT function is to translate expression.
style accepts the same values as the style parameter of the CONVERT function. For more information, see CAST and CONVERT (Transact-SQL).
The range of acceptable values is determined by the value of data_type. If style is null, then TRY_CONVERT returns null.
Return Types
Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.
3. You should also have the questions of "Where do I find more information about the "style" argument?" and "How do I determine what can be explicitly converted?"
To find the answer to those questions, you could look at the "SEE ALSO" section at the bottom of the page but, in this case, you should already know because the "Arguments" section above already has a link. If you click on that link, it will take you to a page that reveal such "secrets". You should read and practice this whole page because it's the KEY to solving a whole lot of issues in T-SQL but you'll also find the answer to what is explicitly allowed and disallowed for conversions as well as what is implicitly allowed, which is the answer to a "bonus question" that you probably hadn't thought to ask (I didn't when I first encountered this page more than 2 decades ago in the real "BOL") in the form of the following chart.
Looky there... A complete From/To diagram of what is possible (anything without a Red "X" in it), things that will implicitly convert (which can also be explicitly converted) , and those things that will convert but require and explicit conversion. It even has some "hot-skinny" about loss of precision and XML.
And, even better, now you know WHERE to find all of this for the next time you have a question (bookmark it... you'll be using it a lot!). That's important because a lot of the answers you get on a forum will 1) make you wait for an answer until someone answers it and 2) might be flat out wrong, partially wrong, wrong for your question, or the person doing the answering might not be sure.
If you're studying for certs, knowing all the stuff on this page is also going to help and it's certainly going to help your career but certs aren't what people are trying to hire you for. Good DBAs and Developers make a lot of money but they need to know a whole lot of stuff off the top of their head. That one interview where you don't know this type of thing can easily keep you from getting paid like one because you can't prove that you are. 😉 You don't need to memorize the entire list of style numbers but you should memorize some of the more common ones and you should also memorize what CONVERT can actually do (binary conversions, for example, come up a lot more than people would think).
You'll also be able to answer the question of what the primary difference between CAST and CONVERT is (CAST has no "style" argument).
Again, this isn't meant as a slam. I just see someone with a great interest in T-SQL and SQL Server falling back on what a lot of people do and that's relying on forums rather than themselves. Don't settle for it... the difference between everyone else and "good" DBAs is largely where to find the answers. The difference between "good" DBAs and "great" DBAs is having practiced it to the point where they actually know the "best" way to do it without even looking anymore and can teach others. 😀
Be one of the "great" ones, Michael... it's not my job to judge but... I see it in you.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff,
Thank you for your post. I can tell it took a while to write it. Before I do a post, I try to look at the online documentation first. I could probably be better at it. In this case, I noticed that comment about not being explicitly allowed. I wasn't sure how SQL was defining that, so my last hope was to post a question. But I like your suggestions. I am always asking myself what happens if it do this or that, but having read your suggestions makes, I think that really helps a lot. I think others can really benefit from reading your posts.
I do remember coming across that diagram. I can't remember if I added that to my notes, but I did this time just to be sure.
-
Good Sir... thank you for taking it the right way. A lot of people would have taken it all offensively. You DO have the right stuff, Michael.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
This was removed by the editor as SPAM
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply