Welcome to OGeek Q&A 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 Reply

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...