TRY_CONVERT large numbers

  • DaveCoventBridge

    Old Hand

    Points: 347

    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.

  • Jacob Wilkins

    One Orange Chip

    Points: 27891

    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!

  • Revenant

    SSC-Forever

    Points: 42467

    Thanks to Steve for a nice QotD and to Hugo for his interesting comments.

  • Rich Mechaber

    SSChampion

    Points: 10935

    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

  • Jacob Wilkins

    One Orange Chip

    Points: 27891

    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!

  • MMartin1

    One Orange Chip

    Points: 27502

    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.

    ----------------------------------------------------
    How to post forum questions to get the best help [/url]

  • Account Closed

    Ten Centuries

    Points: 1041

    Silly me...ran against a db @ compatibility level 100..ooppss

    Account Closed

  • Ed Wagner

    SSC Guru

    Points: 286985

    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.

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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>


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Rich Mechaber

    SSChampion

    Points: 10935

    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

  • MMartin1

    One Orange Chip

    Points: 27502

    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.

    ----------------------------------------------------
    How to post forum questions to get the best help [/url]

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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!


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Rich Mechaber

    SSChampion

    Points: 10935

    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

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the question.

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    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 31 total)

You must be logged in to reply to this topic. Login to reply