如何将varchar（4）转换为在SQL Server 2008中浮动？

问题描述：

我试图将我的数据库字段从VARCHAR(4)转换为FLOAT。这些字段中的某些值可能不是数字，因为这些字段之前没有任何验证。我的主要目标是转换浮点格式的任何integer或decimal值并保存在新的数据库字段中。对于这个过程，我使用旧表中的INSERT SELECT STATEMENT到新表中。到目前为止，我有这行代码为我的转换：如何将varchar（4）转换为在SQL Server 2008中浮动？

CASE WHEN LEN(LTRIM(RTRIM(hs_td2))) <> 0 AND ISNUMERIC(hs_td2) = 1 THEN CAST(LTRIM(RTRIM(hs_td2)) AS float) ELSE NULL END AS hs_td2

第一步我修剪则值检查它是否是数字，然后转换为浮动否则设置为NULL。有了上面的代码，我在微软工作室收到此错误信息：

Msg 8114, Level 16, State 5, Line 13 
Error converting data type varchar to float.

线13日开始我SELECT发言。然后我也尝试了这种转换：

CASE WHEN LEN(LTRIM(RTRIM(hs_td2))) <> 0 AND ISNUMERIC(hs_td2) = 1 THEN CONVERT(FLOAT, LTRIM(RTRIM(hs_td2))) ELSE NULL END AS hs_td2

和我得到了同样的错误信息。在我的字段中的值可能是这样的：

10或5至10或0.9或11.6或-11.89等等...

我想知道如果isNumeric()是最好的功能，我应该使用和为什么我的代码产生上面列出的错误信息？

如果有人可以帮忙，请让我知道。谢谢！

可能不是那些值。 'select cast（' - 11.89'as float）'执行成功。 –

@DanBracuk我不知道是否NULL值可能会导致一些问题？ –

尝试'hs_td2 NOT LIKE '％[^ 0-9]％' 或 ' - ' + hs_td2 NOT LIKE '％[^ 0-9]％''（反转的条件，如果你第一次使用了'SELECT'希望看到无效值）。这仍然不能保证转换会成功（'1.1.1'通过这样的方式滑动），但至少它过滤字母数字。 –

答

不，ISNUMERIC不是最好的功能使用。

从本质上讲，这个问题已经被问过，但不是在这个措辞：

Try_Convert for SQL Server 2008 R2

最upvoted答案建议转换为XML使用XML专用铸件功能：

DECLARE @T TABLE (v varchar(4)); 

INSERT INTO @T (v) VALUES 
('1g23'), 
('-1.8'), 
('11.6'), 
('akjh'), 
('.'), 
('-'), 
('$'), 
('12,5'); 


select 
    cast('' as xml).value('sql:column("V") cast as xs:float ?', 'float') as new_v 
from @T

我会在下面留下我的第一个答案。

很有可能你会得到转换错误，因为服务器试图为表的每一行运行CAST(LTRIM(RTRIM(hs_td2)) AS float)，不仅仅是那些数字的。

当您尝试使用WHERE ISNUMERIC(...) = 1过滤器过滤掉非数字行时，通常会发生这种情况。从技术上讲，它也可能发生在CASE表达式中。

这就是为什么他们在2012年

我想尝试编写使用TRY-CATCH，并试图为给定值转换成我自己的用户自定义函数添加TRY_CONVERT。是的，它会很慢。

说了这么多，下面CASE运行正常的例子：

DECLARE @T TABLE (v varchar(4)); 

INSERT INTO @T (v) VALUES 
('123'), 
('-1.8'), 
('11.6'), 
('akjh'), 
('123'); 

SELECT 
    CASE WHEN ISNUMERIC(v) = 1 THEN CAST(v AS float) ELSE NULL END AS new_v 
FROM @T;

结果

+-------+ 
| new_v | 
+-------+ 
| 123 | 
| -1.8 | 
| 11.6 | 
| NULL | 
| 123 | 
+-------+

但是，如果我把一个.或-或$值，如所以：

INSERT INTO @T (v) VALUES 
('123'), 
('-1.8'), 
('11.6'), 
('akjh'), 
('$');

查询失败：

误差变换数据类型为varchar浮动。

可能还有其他特殊字符及其组合，ISNUMERIC不会抱怨。这就是我最初说整体而言，ISNUMERIC不是最好的功能。

如果这是一个一次性的转换，你可以尝试建立一个LIKE表情捕捉所有特殊情况是存在于你的数据，但如果你需要一个可靠的通用解决方案，升级到2012+和使用TRY_CONVERT或编写你的T-SQL UDF或你的CLR UDF。

那么由于某种原因，我的发言是失败的可能的复制...我在SQL Studio中看到很少的行，然后显示错误消息。我不确定哪个值导致语句失败。 –

此代码将失败，如下所示：INSERT INTO @T（v）VALUES （'12，4'） – sepupic

@sepupic，是的，'ISNUMERIC'不是最好的函数，正如我从一开始就说的那样。 –

答

这取决于值的VARCHAR列

ISNUMBER（）用于vaule如''和' - '将返回1，但是，它会失败当你施放到FLOAT

ISNUMBER（）的值，如'3D2'，'1E2' 将返回1，可CAST FLOAT，但是，你可能不希望将其视为数字。

你可以试试下面的转换

CASE WHEN 
    not LTRIM(RTRIM(hs_td2))like '%[^0-9.,-]%' -- Value only contains 0-9 . - 
    and LTRIM(RTRIM(hs_td2)) not like '.'   -- can not be only . 
    and LTRIM(RTRIM(hs_td2)) not like '-'   -- can not be only - 
    and isnumeric(LTRIM(RTRIM(hs_td2))) = 1 
THEN CAST(LTRIM(RTRIM(hs_td2)) AS float) 
ELSE NULL 
END

如何防止case语句中的非数字值？我也应该使用LTRIM（RTRIM（Value））？ –

使用上面提供的语句，每行的hs_td2列中的所有值均为NULL。 –

它似乎你有空间。您可能需要用LTRIM替换hs_td2（RTRIM（hs_td2）） – EricZ

答

SQLXML有足够的力量使魔。当然，性能是问题。但仍然比百万条件更好

DECLARE @T TABLE (v varchar(4)); 

INSERT INTO @T (v) VALUES 
('123'),('-1.8'),('11.6'),('akjh'),('$'),('-.'),('-.1'),(NULL); 

declare @Xml xml = (SELECT v FROM @T T for xml auto,elements); 

select T.v.value('v[1]','varchar(4)') v, T.v.value('max(v[1])','float') converted_v 
from @xml.nodes('/T') T(v);

如何将varchar（4）转换为在SQL Server 2008中浮动？

相关推荐