将float强制转换为varchar

Question

我有一个包含变量和值的表。values列的格式为varchar(50)。如何转换值，使其显示为"想要"列中的值?

tbody> <<tr>bc

变量	希望		cast(cast([Value] as float) as varchar(50))	cast(cast([Value] as float) as varchar(50))
	77779999	7.778 e + 007	77779999
	-0.56	-0.56	1
	8.21	8.21	8

你可以这样做:

SELECT ISNULL(f, v)  FROM (
SELECT TRY_CONVERT(FLOAT, Value) f , Value v
FROM 
(SELECT CAST(77779999 AS VARCHAR(50)) AS var1, CAST(-0.56 AS VARCHAR(50)) AS var2, CAST(8.21 AS VARCHAR(50)) AS var3) p
UNPIVOT 
(Value FOR ColumnName IN (var1, var2, var3)) AS unpvt
) s;

如果值为float，则将其强制转换为float类型，否则不强制转换。

另一个选项是检查小数点，这输出varchar字段。

SELECT CASE
WHEN Value LIKE '%.%' THEN CONVERT(VARCHAR(50), TRY_CONVERT(FLOAT, Value))
ELSE Value
END AS [wanted]
FROM 
(SELECT CAST(77779999 AS VARCHAR(50)) AS var1, CAST(-0.56 AS VARCHAR(50)) AS var2, CAST(8.21 AS VARCHAR(50)) AS var3, CAST('aa444' as varchar(50)) AS var4) p
UNPIVOT 
(Value FOR ColumnName IN (var1, var2, var3, var4)) AS unpvt