March 2, 2016 at 6:23 am
SELECT TRY_CONVERT(NVARCHAR(1), 234523) also gave me an error but SELECT CONVERT(NVARCHAR(1), 234523) rendered the * . I'm on SQL 2014 so I'm guessing it might be different with 2012 since that was stated in the question.
I also tried this on my 2014 SSMS ruining it on a database set to compatibility level 110 and the result was NULL.
March 2, 2016 at 6:58 am
DaveGlobalOptions (3/2/2016)
SELECT TRY_CONVERT(NVARCHAR(1), 234523) also gave me an error but SELECT CONVERT(NVARCHAR(1), 234523) rendered the * . I'm on SQL 2014 so I'm guessing it might be different with 2012 since that was stated in the question.I also tried this on my 2014 SSMS ruining it on a database set to compatibility level 110 and the result was NULL.
Did the commands tried maybe get mixed up?
The SELECT CONVERT(NVARCHAR(1)... should return an error, not a '*'. When the destination data type is too short, conversion from int to varchar will return a '*', but conversion from int to nvarchar will return an error.
Since conversion to a too-short nvarchar will return an error, using TRY_CONVERT on it will return NULL, as you described. The question was for a TRY_CONVERT to a too-short varchar. If you double-check and make sure the data types you're using are the ones expected, do you still get a different result than indicated by the question?
Cheers!
March 2, 2016 at 9:45 am
Thanks to Steve for a nice QotD and to Hugo for his interesting comments.
March 2, 2016 at 9:52 am
Great question, Steve, thank you for exposing this. Anyone else find BOL more than less than unhelpful here?
Return Types
Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.
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.
OK, great: I get back a converted value, NULL, or an error. Right? Right??
Nope, I get an asterisk.
Are there other undocumented return value options for TRY_CONVERT() anyone knows about?
Capt. Rich Sparrow
March 2, 2016 at 9:58 am
Rich Mechaber (3/2/2016)
Great question, Steve, thank you for exposing this. Anyone else find BOL more than less than unhelpful here?Return Types
Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.
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.
OK, great: I get back a converted value, NULL, or an error. Right? Right??
Nope, I get an asterisk.
Are there other undocumented return value options for TRY_CONVERT() anyone knows about?
Capt. Rich Sparrow
The asterisk is a documented return value of CONVERT (although as discussed in a recent QotD, not particularly well). So the CONVERT succeeds (well, really it completes without error; calling the return of the asterisk "success" might be a bit much), and you get that value back.
Clear as mud, eh? Seems up to the usual MS standards, at least 🙂
Cheers!
March 2, 2016 at 4:30 pm
Without the try_convert function built in, I just did a simple
select convert(varchar(1), 234523); and got back the asterisk. What the meaning was when deciding to do this, I do not know. I would like something like this to fail in code.
----------------------------------------------------
March 3, 2016 at 2:34 am
Silly me...ran against a db @ compatibility level 100..ooppss
Account Closed
March 3, 2016 at 5:29 am
MMartin1 (3/2/2016)
Without the try_convert function built in, I just did a simple
select convert(varchar(1), 234523);and got back the asterisk. What the meaning was when deciding to do this, I do not know. I would like something like this to fail in code.
Agreed. This was likely a very early thing where Microsoft was trying to save us from ourselves. Personally, if something isn't written properly, I think it should fail. I'd rather know about it by having some nice, red, easy-to-find error thrown in my face. It's such a simple one that maybe Microsoft thought differently. Of course, changing the behavior now comes with repercussions, but that's another discussion.
March 4, 2016 at 7:37 am
MMartin1 (3/2/2016)
Without the try_convert function built in, I just did a simple
select convert(varchar(1), 234523);and got back the asterisk. What the meaning was when deciding to do this, I do not know. I would like something like this to fail in code.
<speculation>
This is an inheritance from the dark past.
Varchar is an older data type than nvarchar. The original behaviour of CONVERT was to not fail an entire set-based operation on a single error (a behaviourt that by the way is still often requested for many other operations), but allow it to continue - so the values that do not fit are presented as *, as a special "hey you might want to look into this" token.
When nvarchar was introduced, people were more in line with your and Ed's statement that a conversion that fails should in fact fail the operation. So for nvarchar, the behaviour was to error if the CONVERT fails. For backwards compatiility reasons, varchar was unchanged.
And when TRY_CONVERT was introduced, the idea was to stop CONVERT from erroring out a whole batch and instead use a special token -NULL in this case- to represent cases where CONVERT would otherwise fail. For long number into nvarchar, that was a failure so you get NULL. For long number into varchar, it was not - so this still returns the "old" token.
</speculation>
March 4, 2016 at 8:07 am
Thanks for the speculation, Hugo.
Huh. I guess in this case, the "N" in NVARCHAR must stand for "NULL"? Or maybe "Not gonna return an asterisk if the conversion fails?"
SELECT TRY_CONVERT(NVARCHAR(1), 234523)
🙂
Rich
March 4, 2016 at 12:44 pm
Hugo Kornelis (3/4/2016)
MMartin1 (3/2/2016)
Without the try_convert function built in, I just did a simple
select convert(varchar(1), 234523);and got back the asterisk. What the meaning was when deciding to do this, I do not know. I would like something like this to fail in code.<speculation>
This is an inheritance from the dark past.
Varchar is an older data type than nvarchar. The original behaviour of CONVERT was to not fail an entire set-based operation on a single error (a behaviourt that by the way is still often requested for many other operations), but allow it to continue - so the values that do not fit are presented as *, as a special "hey you might want to look into this" token.
When nvarchar was introduced, people were more in line with your and Ed's statement that a conversion that fails should in fact fail the operation. So for nvarchar, the behaviour was to error if the CONVERT fails. For backwards compatiility reasons, varchar was unchanged.
And when TRY_CONVERT was introduced, the idea was to stop CONVERT from erroring out a whole batch and instead use a special token -NULL in this case- to represent cases where CONVERT would otherwise fail. For long number into nvarchar, that was a failure so you get NULL. For long number into varchar, it was not - so this still returns the "old" token.
</speculation>
I think your speculation makes sense Hugo. Thus when applying the TRY_CONVERT in these situations you will have to check for NULL or * to test if the conversion succeeded , depending on the columns data type. It still interesting to me that the returning of the '*' is not consistent with other conversion errors.
select convert(varchar(1), 123.3) throws an error. I wonder why int to varchar was special.
----------------------------------------------------
March 5, 2016 at 9:05 am
MMartin1 (3/4/2016)
I wonder why int to varchar was special.
I am sure that sometime, somewhere deep in the dark and muddy past of SQL Server (probably back in the Sybase days), someone had a compelling argument for this.
I am not going to speculate on this one, though!
March 7, 2016 at 7:49 am
Maybe everyone following this already knew it, but as I don't use CONVERT much at all, I had to read through the BOL entry Steve linked to for CONVERT.
One thing I learned: under the section heading of "Truncating and Rounding Results" it states that -- when converting INT, SMALLINT, or TINYINT to CHAR or VARCHAR -- "*" will be returned if truncation would occur.
Note: BIGINT is not in that list in BOL.
Thus, this fails correctly (to my mind) and returns NULL, not an asterisk:
SELECT TRY_CONVERT(VARCHAR(1), CAST(123 As BIGINT));
Rich
March 10, 2016 at 4:31 pm
Thanks for the question.
March 11, 2016 at 12:35 am
Great question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply