Len function on Float in SQLServer gives wrong length

Question

I am using the below query in SQL Server.

declare @dt float
set @dt = 1079938.05
select @dt AS Val,Convert(nvarchar(20),@dt) AS NVal, len(@dt) AS Len

Its output is

Val         NVal            Len
1079938.05  1.07994e+006    12

My questions are:

1. 'Val' column shows right value.
2. 'NVal' column shows strange value please explain us why it shows like this?
3. 'Len' shows length and its actual length is 10 but it shows us 12. Please explain why it shows 12 instead of 10.

Answer 1

A float in sql server can be 4 or 8 byte. Find details.

LEN() is a function to measure the lenght of a string. So you want to measure the length of the string representation of the value, not the value itself.

The shown display value 1.07994e+006 is scientific notation and has 12 characters. Nothing wrong here.

Your call Convert(nvarchar(20),@dt) calls the CONVERT()-function with the defaul for FLOAT and REAL(Details and other formats here), which is scientific for numbers larger than 6 digits. The same happens implicitly when you call 'len(@dt)'. As the input of LEN() must be a string, the value is converted and then passed to the function.

What you can do:

• You might think about a conversion to DECIMAL...

• Another choice was first to use STR()-function together with RTRIM().

• One more choice was FORMAT()-function (SQL Server 2012+) .

Anyway you have to consider, that the text you see is not the real value.

Answer 2

LEN() works on [N]VARCHAR(), thus you're running into an implicit conversion from FLOAT to VARCHAR

see this: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a4ea2bc1-6f2f-4992-8132-f824fe4ffce0/length-of-float-values-in-ms-sql-server-gives-wrong-result?forum=transactsql

That means that LEN converts the value to VARCHAR before it actually calculates its length. That's because the length you get coincides with the length of your NVarChar value 1.07994e+006.