我有一个包含变量和值的表。values列的格式为varchar(50)。如何转换值,使其显示为"想要"列中的值?
变量 | 希望 | cast(cast([Value] as float) as varchar(50)) | cast(cast([Value] as float) as varchar(50)) | 77779999 | 7.778 e + 007 | 77779999 | b
---|---|---|---|---|
-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