LEN and CONVERT

  • This was removed by the editor as SPAM

  • Miles Neale

    SSChampion

    Points: 13147

    Revenant (7/30/2013)


    Nice and easy - thanks, Kapil!

    +1

    Not all gray hairs are Dinosaurs!

  • PHYData DBA

    SSCertifiable

    Points: 7541

    Nice and easy question.

  • PHYData DBA

    SSCertifiable

    Points: 7541

    My two cents on why answer one is 12 and not 14 is another question.

    Is money a numeric type?

    http://msdn.microsoft.com/en-us/library/ms187752.aspx

    If it is then isn't the length the number of bytes used to store the value?

    http://msdn.microsoft.com/en-us/library/ms190476.aspx

    Am I missing something?

  • PHYData DBA

    SSCertifiable

    Points: 7541

    PHYData DBA (7/30/2013)


    My two cents on why answer one is 12 and not 14 is another question.

    Is money a numeric type?

    http://msdn.microsoft.com/en-us/library/ms187752.aspx

    If it is then isn't the length the number of bytes used to store the value?

    http://msdn.microsoft.com/en-us/library/ms190476.aspx

    Am I missing something?

    No... Not missing a thing...

    DECLARE @var money = 123457756.0000

    --Select 1

    SELECT LEN(@var)

    This also returns 12 even though the character count is 14.

    Of course if you convert the money or numeric data type to a Character Data type then LEN will return the number of characters and not the bytes to store. 😎

    So in this instance, style means nothing and data size is everything :smooooth:

  • PHYData DBA

    SSCertifiable

    Points: 7541

    thierry.vandurme (7/30/2013)


    Hi Philip

    that indeed explains it. Thx a lot! I was almost losing my mind over this 🙂

    Chrz

    Thierry

    Thierry,

    That explanation is not correct for TSQL. Style does not affect the Length of a numeric data type. The length of numeric is defined by the amount of bytes required to store them. Money values will be around 12 to 18 bytes.

  • TomThomson

    SSC Guru

    Points: 104772

    PHYData DBA (7/30/2013)


    thierry.vandurme (7/30/2013)


    Hi Philip

    that indeed explains it. Thx a lot! I was almost losing my mind over this 🙂

    Chrz

    Thierry

    Thierry,

    That explanation is not correct for TSQL. Style does not affect the Length of a numeric data type. The length of numeric is defined by the amount of bytes required to store them. Money values will be around 12 to 18 bytes.

    PHYData, that, and also your immediately preceding post, are exactly the opposite of correct. You can easily see that by noting that the storage sizes for the money and decimal typed values quoted are 8, 5, and 5, not 12, 5 and 8. Or equally easily by noting that LEN delivers the number of characters in a string excluding trailing blanks, and has nothing to do with storage size.

    The way the question works is that LEN requires a character string argument, so there is an implicit conversion from money or decimal to string. That implicit conversion uses the default style for numeric to string conversions, which is 0 (as stated on the Cast and Convert BOL page) and that's why the length for the numeric value is 12 rather than 14 - Philip's explanation to which Thierry was responding is exactly correct. The question is about the lengths of the strings to which the numeric values are implicitly converted, since the length operator used is LEN not DATALENGTH while the function which delivers storage size is DATALENGTH, not LEN.

    Tom

  • Narud

    SSCrazy

    Points: 2819

    L' Eomot Inversé (7/30/2013)

    The way the question works is that LEN requires a character string argument, so there is an implicit conversion from money or decimal to string. That implicit conversion uses the default style for numeric to string conversions, which is 0 (as stated on the Cast and Convert BOL page) and that's why the length for the numeric value is 12 rather than 14 - Philip's explanation to which Thierry was responding is exactly correct. The question is about the lengths of the strings to which the numeric values are implicitly converted, since the length operator used is LEN not DATALENGTH while the function which delivers storage size is DATALENGTH, not LEN.

    Thank you Tom, for giving us a better explanation one more time 🙂

  • kapil_kk

    SSC-Insane

    Points: 21316

    L' Eomot Inversé (7/30/2013)


    PHYData DBA (7/30/2013)


    thierry.vandurme (7/30/2013)


    Hi Philip

    that indeed explains it. Thx a lot! I was almost losing my mind over this 🙂

    Chrz

    Thierry

    Thierry,

    That explanation is not correct for TSQL. Style does not affect the Length of a numeric data type. The length of numeric is defined by the amount of bytes required to store them. Money values will be around 12 to 18 bytes.

    PHYData, that, and also your immediately preceding post, are exactly the opposite of correct. You can easily see that by noting that the storage sizes for the money and decimal typed values quoted are 8, 5, and 5, not 12, 5 and 8. Or equally easily by noting that LEN delivers the number of characters in a string excluding trailing blanks, and has nothing to do with storage size.

    The way the question works is that LEN requires a character string argument, so there is an implicit conversion from money or decimal to string. That implicit conversion uses the default style for numeric to string conversions, which is 0 (as stated on the Cast and Convert BOL page) and that's why the length for the numeric value is 12 rather than 14 - Philip's explanation to which Thierry was responding is exactly correct. The question is about the lengths of the strings to which the numeric values are implicitly converted, since the length operator used is LEN not DATALENGTH while the function which delivers storage size is DATALENGTH, not LEN.

    Thanks TOM for your great explanation.......

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • john.arnott

    SSChampion

    Points: 11882

    L' Eomot Inversé (7/30/2013)


    ...... The question is about the lengths of the strings to which the numeric values are implicitly converted, since the length operator used is LEN not DATALENGTH while the function which delivers storage size is DATALENGTH, not LEN.

    On the other hand, how would that explain this result I get (with SQL 2005)?

    I set a "money" variable to a value with four decimal places, the SELECT of that variable returns all digits, yet the value returned by the LEN() function is two short.

    declare @var money;

    set @var = 12345.1234;

    select @var, len(var);

    -- returns: 12345.1234 8

  • Christian Buettner-167247

    SSChampion

    Points: 13729

    john.arnott (7/31/2013)


    L' Eomot Inversé (7/30/2013)


    ...... The question is about the lengths of the strings to which the numeric values are implicitly converted, since the length operator used is LEN not DATALENGTH while the function which delivers storage size is DATALENGTH, not LEN.

    On the other hand, how would that explain this result I get (with SQL 2005)?

    I set a "money" variable to a value with four decimal places, the SELECT of that variable returns all digits, yet the value returned by the LEN() function is two short.

    declare @var money;

    set @var = 12345.1234;

    select @var, len(var);

    -- returns: 12345.1234 8

    It was already mentioned above - the value is cast to a string with style 0.

    But it is easier if you see the result:

    DECLARE @VAR MONEY = 123457756.1234

    --SELECT 1

    PRINT '<' + CAST(@VAR AS VARCHAR(1000)) + '>'

    <123457756.12>

    Best Regards,

    Chris Büttner

  • philip.cullingworth

    SSCrazy

    Points: 2150

    john.arnott (7/31/2013)


    L' Eomot Inversé (7/30/2013)


    ...... The question is about the lengths of the strings to which the numeric values are implicitly converted, since the length operator used is LEN not DATALENGTH while the function which delivers storage size is DATALENGTH, not LEN.

    On the other hand, how would that explain this result I get (with SQL 2005)?

    I set a "money" variable to a value with four decimal places, the SELECT of that variable returns all digits, yet the value returned by the LEN() function is two short.

    declare @var money;

    set @var = 12345.1234;

    select @var, len(var);

    -- returns: 12345.1234 8

    John

    LEN takes a string_expression parameter (http://msdn.microsoft.com/en-us/library/ms190329%28v=sql.90%29.aspx) and so SQL has to do an implicit conversion on the money variable in order to work out the length.

    The default style when converting money to string is 0 which means No commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; LEN(@var) is equivalent to LEN(CONVERT(VARCHAR(50),@var,0) which means your money variable gets converted to the string '12345.12' with a length of 8.

    The other style options are 1 Commas every three digits to the left of the decimal point, and two digits to the right of the decimal point so LEN(CONVERT(VARCHAR(50),@var,1) would give '12,345.12' with a length of 9; and 2 No commas every three digits to the left of the decimal point, and four digits to the right of the decimal point which would give LEN(CONVERT(VARCHAR(50),@var,2) returning '12345.1234' with a length of 10.

    The whole confusion over this appears to do with the fact that LEN returns the length of a string, requiring an implicit conversion when the parameter is not a string.

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    john.arnott (7/31/2013)


    L' Eomot Inversé (7/30/2013)


    ...... The question is about the lengths of the strings to which the numeric values are implicitly converted, since the length operator used is LEN not DATALENGTH while the function which delivers storage size is DATALENGTH, not LEN.

    On the other hand, how would that explain this result I get (with SQL 2005)?

    I set a "money" variable to a value with four decimal places, the SELECT of that variable returns all digits, yet the value returned by the LEN() function is two short.

    declare @var money;

    set @var = 12345.1234;

    select @var, len(var);

    -- returns: 12345.1234 8

    SELECT @var returns the data to the client in its native format - i.e., as a money datatype. The client formats it for presentation purposes. In SSMS, this formatting may depend on WIndows locale settings (I don't know if it actually does for money; I do know float values are formatted differently on localized Windows installations). If you run the code in osql.exe, you might (again, untested - just saying it's possible) see even different formatting.

    SELECT LEN(@var) implicitly converts @var to string, because that's the only data type supported for LEN. This conversion is independent of the clients' Windows locale setting, since it's done at the server. It might depend on SET LANGUAGE, though I'd expect that to only influence the choice of decimal separator. The length of the string is then computed and returned to the client as an integer. The client then decides on how to present this integer value. Most clients tend to do this in the same way, since there aren't that many options for formatting the string representation of integer values.


    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/

  • Bangla

    Hall of Fame

    Points: 3137

    Question was simple... but this discussion was excellent....

  • PHYData DBA

    SSCertifiable

    Points: 7541

    Hugo Kornelis (8/1/2013)SELECT LEN(@var) implicitly converts @var to string, because that's the only data type supported for LEN. This conversion is independent of the clients' Windows locale setting, since it's done at the server. It might depend on SET LANGUAGE, though I'd expect that to only influence the choice of decimal separator. The length of the string is then computed and returned to the client as an integer. The client then decides on how to present this integer value. Most clients tend to do this in the same way, since there aren't that many options for formatting the string representation of integer values.

    Hugo - I would wager you have no documentation or proof that this conversion occurs.

    LEN returns the Bytes required to store the data for All NUMERIC data types. It does not convert the value to string.

Viewing 15 posts - 16 through 30 (of 46 total)

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