将varchar转换为INT [英] converting varchar to INT

问题描述

您好SQL Server专家，

Hi SQL Server Experts,

有人知道，为什么我的转换失败了？我四处搜索并想出了那些演员函数，但似乎都没有。

Does someone know, why my conversion fails? I searched around and came up with those casting functions but none seems to work.

create table orders
(
hoursworked varchar(50)
);

INSERT INTO orders (hoursworked) VALUES ('8,5');

select 
CAST(REPLACE(hoursworked,',','.') AS INT), 
CONVERT(INT,
        CASE
        WHEN IsNumeric(CONVERT(VARCHAR(12), hoursworked)) = 1 THEN CONVERT(VARCHAR(12),hoursworked)
        ELSE 0 END) 
from orders where hoursworked ='8,5'

推荐答案

原因8.5是浮点数。

Cause 8.5 is a float.

例如

DECLARE @orders TABLE (
    hoursworked VARCHAR(50)
);

INSERT INTO @orders ( hoursworked )
VALUES ( '8,5' );

SELECT CAST(REPLACE(hoursworked, ',', '.') AS NUMERIC(9, 2)) ,
       CONVERT(NUMERIC(9, 2), REPLACE(hoursworked, ',', '.'))
FROM   @orders
WHERE  hoursworked = '8,5';

此外，我看不出感觉在您的ISNUMERIC（）测试中。使用
TRY_CAST（） /
TRY_PARSE（）代替：

Also I don't see the sense in your ISNUMERIC() test. Use TRY_CAST() / TRY_PARSE() instead:

DECLARE @orders TABLE (
    hoursworked VARCHAR(50)
);

INSERT INTO @orders ( hoursworked )
VALUES ( '8,5' );

SELECT TRY_PARSE(hoursworked AS NUMERIC(9, 2)) ,
       TRY_PARSE(hoursworked AS NUMERIC(9, 2) USING 'de-DE') ,
       TRY_CAST(hoursworked AS NUMERIC(9, 2)) ,
       TRY_CAST(REPLACE(hoursworked, ',', '.') AS NUMERIC(9, 2))
FROM   @orders
WHERE  hoursworked = '8,5';

这篇关于将varchar转换为INT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！