Len function on Float in SQLServer gives wrong len

问题:

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.

回答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.

回答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.

回答3:

First of all: Don't use approximate data types when not forced to. A FLOAT is just an approximation, e.g. a simple value like 0.123 may be stored as 0.1230000000001 for instance. Use a precise type such as DECIMAL instead.

When converting a number to a string, you should usually specify a format as in format(@dt, '#,###,##0.00'). You don't do so, so it's up to the system what format to use. It uses a scientific notation 1.07994e+006 translating to 1.079940 x 10^6, which is approximately your number.