LEN and CONVERT

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 More actions · Answer 5

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: 104773 More actions · Answer 6

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: 2837 More actions · Answer 7

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 More actions · Answer 8

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 More actions · Answer 9

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 More actions · Answer 10

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 More actions · Answer 11

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: 64790 More actions · Answer 12

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 More actions · Answer 13

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

PHYData DBA SSCertifiable Points: 7541 More actions · Answer 14

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.