Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.3k views
in Technique[技术] by (71.8m points)

sql - Len function on Float in SQLServer gives wrong length

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.
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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:

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...