More TRY_CONVERTing numbers

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714297

    Comments posted to this topic are about the item More TRY_CONVERTing numbers

  • This was removed by the editor as SPAM

  • twin.devil

    SSC-Insane

    Points: 22208

    Good question, as this behavior is expected, same for NCHAR/CHAR as well.

    -- For NCHAR

    SELECT TRY_CONVERT(NCHAR(1), 234523)

    Result :

    ----

    NULL

    (1 row(s) affected)

    -- For CHAR

    SELECT TRY_CONVERT(CHAR(1), 234523)

    Result :

    ----

    *

    (1 row(s) affected)

    Thanks for sharing Steve.

  • Toreador

    SSChampion

    Points: 11220

    Although the documentation states that conversion of an int value that is too long will give an error if converting to nvarchar, but * if converting to varchar, it does not say why there is a difference. Does anyone know?

  • paul s-306273

    SSChampion

    Points: 10526

    I think I need to read up on this subject...

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    From the explanation:

    TRY_CONVERT should obey the CAST and CONVERT rules, which note that truncated values are returned as an E for char and varchar. However, a NULL was returned on multiple machines.

    The description for CAST and CONVERT includes a table that specifies the expected results in various "won't fit" cases, showing either * or E depending on input and output data type. However, this is not a literal representation of the expected output. The footnote below the table clarifies that E means that an error is returned because the result length is too short.

    And because CONVERT should return an error in this case, TRY_CONVERT should return NULL. The result is not as surprising as the quote above suggests.


    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/

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    Toreador (4/21/2016)


    Although the documentation states that conversion of an int value that is too long will give an error if converting to nvarchar, but * if converting to varchar, it does not say why there is a difference. Does anyone know?

    The bane of backwards compatibility. Old versions of SQL Server (and this dates back to the Sybase times) returned * when the result would not fit. An error would have been more logical, so when new data types were introduced they were given that "better" behaviour. Making the same change for already existing data types was not done because that might invalidate existing logic. The SQL Server team usually takes backwards compatibility very seriously.


    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/

  • TomThomson

    SSC Guru

    Points: 104707

    Hugo Kornelis (4/21/2016)


    From the explanation:

    TRY_CONVERT should obey the CAST and CONVERT rules, which note that truncated values are returned as an E for char and varchar. However, a NULL was returned on multiple machines.

    The description for CAST and CONVERT includes a table that specifies the expected results in various "won't fit" cases, showing either * or E depending on input and output data type. However, this is not a literal representation of the expected output. The footnote below the table clarifies that E means that an error is returned because the result length is too short.

    And because CONVERT should return an error in this case, TRY_CONVERT should return NULL. The result is not as surprising as the quote above suggests.

    Quite apart from not taking account of the footnote, that part of the explanation is totally wrong. It is clearly documented (on the cast and convert page referenced) that for varchar and char truncation in convert leads not to E but to *. And the suggestion that try_convert returns NULL for truncation with char and varchar is not borne out on either of the machines I have SQLServer 2012 on, so maybe there is a mistype (omitted the initial "n" on the two typenames)? So it seems to me that everything works as documented (even the backward compatibility nastiness), and as you say, none of it is surprising (not even the backwards compatability bringing different results from char and nchar).

    Tom

  • roger.plowman

    SSChampion

    Points: 10114

    When I run that select statement in SQL Server 2008 (10.0.5538) I get the error:

    SELECT TRY_CONVERT(NVARCHAR(1), 234523)

    Msg 195, Level 15, State 10, Line 1

    'NVARCHAR' is not a recognized built-in function name.

    Is there something in my DB settings that would generate this? I'm pretty sure they're using the install defaults.

    Or did I miss some part of the statement when I copied it?

  • Jacob Wilkins

    One Orange Chip

    Points: 27724

    roger.plowman (4/21/2016)


    When I run that select statement in SQL Server 2008 (10.0.5538) I get the error:

    SELECT TRY_CONVERT(NVARCHAR(1), 234523)

    Msg 195, Level 15, State 10, Line 1

    'NVARCHAR' is not a recognized built-in function name.

    Is there something in my DB settings that would generate this? I'm pretty sure they're using the install defaults.

    Or did I miss some part of the statement when I copied it?

    TRY_CONVERT is 2012+.

    Cheers!

  • Jacob Wilkins

    One Orange Chip

    Points: 27724

    TomThomson (4/21/2016)


    Hugo Kornelis (4/21/2016)


    From the explanation:

    TRY_CONVERT should obey the CAST and CONVERT rules, which note that truncated values are returned as an E for char and varchar. However, a NULL was returned on multiple machines.

    The description for CAST and CONVERT includes a table that specifies the expected results in various "won't fit" cases, showing either * or E depending on input and output data type. However, this is not a literal representation of the expected output. The footnote below the table clarifies that E means that an error is returned because the result length is too short.

    And because CONVERT should return an error in this case, TRY_CONVERT should return NULL. The result is not as surprising as the quote above suggests.

    Quite apart from not taking account of the footnote, that part of the explanation is totally wrong. It is clearly documented (on the cast and convert page referenced) that for varchar and char truncation in convert leads not to E but to *. And the suggestion that try_convert returns NULL for truncation with char and varchar is not borne out on either of the machines I have SQLServer 2012 on, so maybe there is a mistype (omitted the initial "n" on the two typenames)? So it seems to me that everything works as documented (even the backward compatibility nastiness), and as you say, none of it is surprising (not even the backwards compatability bringing different results from char and nchar).

    Agreed. Just missing an 'n' on the two data types combined with assuming that the documentation meant a literal 'E' makes the most sense.

    That particular bit of documentation came up fairly recently in another QotD for being a bit odd. For NCHAR and NVARCHAR, the table says 'E', and the legend explains that 'E' means an error will be returned. However, for CHAR and VARCHAR, the table says '*', and that is the literal value returned, not what '*' stands for according to the legend.

    Not surprisingly, MS could have documented it a bit more clearly 🙂

    Cheers!

  • Pher

    SSC Enthusiast

    Points: 125

    Using SQL 2012

    -- Msg 195, Level 15, State 10, Line 5

    -- 'NVARCHAR' is not a recognized built-in function name.

    -- The TRY_Convert function requires 3 argument(s).

    SELECT TRY_CONVERT(NVARCHAR(1), 234523)

    Did I miss something?

  • Jacob Wilkins

    One Orange Chip

    Points: 27724

    Pher (4/21/2016)


    Using SQL 2012

    -- Msg 195, Level 15, State 10, Line 5

    -- 'NVARCHAR' is not a recognized built-in function name.

    -- The TRY_Convert function requires 3 argument(s).

    SELECT TRY_CONVERT(NVARCHAR(1), 234523)

    Did I miss something?

    EDIT: I knee-jerk responded about compatibility level, but I don't think that bit about 3 arguments would come up, just the '...not a recognized built-in function name'.

    I'd still check the compatibility level to make sure it's 110, but something else odd is going on with that one.

    Cheers!

  • Revenant

    SSC-Forever

    Points: 42468

    It looks that Steve hit the nerve with this one. 🙂

  • Alan Burstein

    SSC Guru

    Points: 61006

    I said 2. What is wrong with me.

    -- Alan Burstein


    Helpful links:Best practices for getting help on SQLServerCentral -- Jeff ModenHow to Post Performance Problems -- Gail ShawNasty fast set-based string manipulation functions:For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)To split strings based on patterns try PatternSplitCMNeed to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8KI cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code.  -- Itzik Ben-Gan 2001

Viewing 15 posts - 1 through 15 (of 21 total)

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