SQL Server Cast float to varchar

brvekthn 于 2023-04-04 发布在其他

关注(0)|答案(1)|浏览(138)

I have a table with variables and values. The values column is in varchar(50). How can I cast the values, so they appear as they are in the column "Want"?
| Variable | Want | cast([Value] as varchar(50)) | cast(CAST([Value] AS float) as varchar(50)) |
| ------------ | ------------ | ------------ | ------------ |
| a | 77779999 | 7.778e+007 | 77779999 |
| b | -0.56 | -0.56 | -1 |
| c | 8.21 | 8.21 | 8 |

I'm using Microsoft SQL Server Management Studio.

EDIT:

My original data is as follows and I want to transpose it in one column with type varchar(50) .

select 77779999 as var1, -0.56 as var2, 8.21 as var3

var1 (INT)	var2 (float)	var3 (float)
77779999	-0.56	8.21

sql-server

来源：https://stackoverflow.com/questions/75926512/cast-float-to-varchar

1条答案

按热度按时间

ni65a41a1#

You can do something like this:

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;

If the value is float, then it is cast to a FLOAT, otherwise not.

Another option is check for decimal point, this outputs varchar field.

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

赞(0）回复(0）举报 2023-04-04

我来回答

SQL Server Cast float to varchar

1条答案

相关问题

热门标签

最新问答