SQL 将浮点数转换或转换为 nvarchar？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4881674/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
cast or convert a float to nvarchar?
提问by Eugene
I need to select from one column of datatype float and insert it in another column as nvarchar.
我需要从数据类型 float 的一列中进行选择，并将其作为 nvarchar 插入到另一列中。
I tried to cast it: cast([Column_Name] as nvarchar(50))
我试图投射它： cast([Column_Name] as nvarchar(50))
The result was 9.07235e+009instead of a 10 digit number (phone number).
结果9.07235e+009不是 10 位数字（电话号码）。
Does any one know how to cast or convert this data properly?
有谁知道如何正确地转换或转换这些数据？
回答by a1ex07
回答by Coxy
If you're storing phone numbers in a float typed column (which is a bad idea) then they are presumably all integers and could be cast to int before casting to nvarchar.
如果您将电话号码存储在浮点类型的列中（这是一个坏主意），那么它们可能都是整数，并且可以在转换为 nvarchar 之前转换为 int。
So instead of:
所以而不是：
select cast(cast(1234567890 as float) as nvarchar(50))
1.23457e+009
You would use:
你会使用：
select cast(cast(cast(1234567890 as float) as int) as nvarchar(50))
1234567890
In these examples the innermost cast(1234567890 as float)is used in place of selecting a value from the appropriate column.
在这些示例中，最内层cast(1234567890 as float)用于代替从适当的列中选择值。
I really recommendthat you not store phone numbers in floats though!
What if the phone number starts with a zero?
不过，我真的建议您不要将电话号码存储在浮点数中！
如果电话号码以零开头怎么办？
select cast(0100884555 as float)
100884555
Whoops! We just stored an incorrect phone number...
哎呀！我们刚刚存储了一个错误的电话号码...
回答by RichardTheKiwi
Do notuse floats to store fixed-point, accuracy-required data. This example shows how to convert a float to NVARCHAR(50) properly, while also showing why it is a bad idea to use floats for precision data.
不要使用浮点数来存储需要精度的定点数据。此示例说明如何将浮点数正确转换为 NVARCHAR(50)，同时还说明了为什么将浮点数用于精度数据是一个坏主意。
create table #f ([Column_Name] float)
insert #f select 9072351234
insert #f select 907235123400000000000
select
cast([Column_Name] as nvarchar(50)),
--cast([Column_Name] as int), Arithmetic overflow
--cast([Column_Name] as bigint), Arithmetic overflow
CAST(LTRIM(STR([Column_Name],50)) AS NVARCHAR(50))
from #f
Output
输出
9.07235e+009 9072351234
9.07235e+020 907235123400000010000
You may notice that the 2nd output ends with '10000' even though the data we tried to store in the table ends with '00000'. It is because floatdatatype has a fixed number of significant figures supported, which doesn't extend that far.
您可能会注意到，即使我们尝试存储在表中的数据以“00000”结尾，第二个输出也以“10000”结尾。这是因为float数据类型支持固定数量的有效数字，这并没有扩展那么远。
回答by Pullaiah Mamidala
Float won't convert into NVARCHAR directly, first we need to convert float into money datatype and then convert into NVARCHAR, see the examples below.
Float不会直接转换成NVARCHAR，首先我们需要把float转换成money数据类型，然后再转换成NVARCHAR，看下面的例子。
Example1
示例 1
SELECT CAST(CAST(1234567890.1234 AS FLOAT) AS NVARCHAR(100))
output
输出
1.23457e+009
Example2
例2
SELECT CAST(CAST(CAST(1234567890.1234 AS FLOAT) AS MONEY) AS NVARCHAR(100))
output
输出
1234567890.12
In Example2 value is converted into float to NVARCHAR
Example2 中的值被转换为 float 到 NVARCHAR
回答by Antonis Papadimitriou
DECLARE @MyFloat [float]
声明@MyFloat [浮动]
SET @MyFloat = 1000109360.050
设置@MyFloat = 1000109360.050
SELECT REPLACE (RTRIM (REPLACE (REPLACE (RTRIM ((REPLACE (CAST (CAST (@MyFloat AS DECIMAL (38 ,18 )) AS VARCHAR( max)), '0' , ' '))), ' ' , '0'), '.', ' ')), ' ','.')
SELECT REPLACE (RTRIM (REPLACE (REPLACE (RTRIM ((REPLACE (CAST (CAST (CAST (@MyFloat AS DECIMAL (38 ,18 )) AS VARCHAR( max)), '0' , ' '))), ' ' , '0 '), '.', ' ')), ' ','.')
