将数据类型varchar转换为指定列中的浮点时出错

a0x5cqrl 于 2021-07-27 发布在 Java

关注(0)|答案(2)|浏览(445)

嘿，我正在尝试将数据类型从varchar转换为列名'identification'的float。下面是我创建的查询，它们都抛出了错误

Select distinct Convert(float,(Replace([Identification], '-','')))  FROM [A]
where identification is NOT Null;

Select Cast(Replace([Identification], '-','') AS FLOAT) FROM [A]
where identification is NOT Null;

错误消息：
消息8114，16级，状态5，行1
将数据类型varchar转换为float时出错。

sql sql-server sql-server-2008

来源：https://stackoverflow.com/questions/63178775/error-converting-data-type-varchar-to-float-in-assigned-column

2条答案

按热度按时间

ffvjumwh1#

Haaaz, try this:

   CASE WHEN ISNUMERIC([Identification]) THEN CONVERT(float, 
    [Identification])

赞(0）回复(0）举报 2021-07-27

wswtfjt72#

select cast('' as xml).value('xs:float(sql:column("val"))', 'float')
from (values ('a'),('b'), ('-123'))  v(val);

CREATE TABLE ATest(Identification varchar(100));

INSERT INTO ATest(Identification) 
VALUES('A'),('B'), ('A-B-123'), ('123-456'), ('1-1-1-1-1'), 
('123-E10'), /*you might need to handle this type of values when scientific notation should NOT be converted to float...
....eg. just replace E with another letter*/
('------1------')
;

SELECT
    Identification, ReplacedIdentification, 
    cast('' as xml).value('xs:float(sql:column("src.ReplacedIdentification"))', 'float') as ToFloatOrNull
FROM
(
Select [Identification], Replace([Identification], '-','') ReplacedIdentification
FROM [ATest]
WHERE[Identification] IS NOT Null
) as src
--use this to find Identification values which cannot be converted to float
--WHERE cast('' as xml).value('xs:float(sql:column("src.ReplacedIdentification"))', 'float') IS NULL;

DROP TABLE Atest;
GO

赞(0）回复(0）举报 2021-07-27

我来回答

将数据类型varchar转换为指定列中的浮点时出错

2条答案

相关问题

热门标签

最新问答