如何在SQL Server 2008中将varchar(4)转换为float？

Question

我正在尝试将我的数据库字段从VARCHAR(4)转换为FLOAT。由于这些字段之前没有进行任何验证，因此这些字段中的一些值可能不是数字。我的主要目标是将任何以浮点格式表示的整数或小数值转换并保存到新的数据库字段中。为此，我使用INSERT SELECT STATEMENT从旧表格中选择并插入到新表格中。到目前为止，我已经写出了以下代码来进行转换：

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。使用上面的代码，我在Microsoft Studio中得到以下错误消息：

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()是我应该使用的最佳函数，以及为什么我的代码会产生上述错误消息？

如果有人能帮忙，请告诉我。谢谢！

- espresso_coffee

---

可能不是那些值。select cast('-11.89' as float) 执行成功。 - Dan Bracuk

@DanBracuk 我在想NULL值是否会引起一些问题？ - espresso_coffee

尝试使用 hs_td2 NOT LIKE '%[^0-9.]%' OR '-' + hs_td2 NOT LIKE '%[^0-9.]%'（反转条件并首先使用 SELECT 查看无效值）。 这仍然不能保证转换成功（例如 1.1.1 通过此方式滑过），但至少可以过滤字母数字。 - Jeroen Mostert

-11.89 将无法适应 varchar(4) 字段... - Hans Kesting

@HansKesting 我犯了一个错误。所有的数字都会在小数点后保留一位。 - espresso_coffee

可能是Try_Convert for SQL Server 2008 R2的重复问题。 - Vladimir Baranov

Answer 1

不，ISNUMERIC 不是最好的函数选择。

本质上，这个问题之前已经被问过了，尽管没有用这种措辞：

SQL Server 2008 R2 的 Try_Convert 最受欢迎的答案建议将其转换为 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_CONVERT。

我会尝试编写自己的用户定义函数，该函数使用TRY-CATCH并尝试转换给定值。是的，它会很慢。

---

话虽如此，下面的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数据类型转换为float时出错。

可能会有其他特殊字符及其组合，ISNUMERIC不会报错。这就是我最初说ISNUMERIC不是最好的函数使用的原因。

如果只是一次性的转换，您可以尝试构建一个LIKE表达式来捕获所有存在于您的数据中的特殊情况，但如果您需要一个可靠的通用解决方案，请升级到2012+并使用TRY_CONVERT或编写T-SQL UDF或CLR UDF。

- Vladimir Baranov

---

由于某种原因，我的语句失败了... 我在SQL Studio中看到几行，然后出现错误消息。我不确定是哪个值导致语句失败。 - espresso_coffee

这段代码将无法处理类似以下的情况：INSERT INTO @T (v) VALUES ('12,4')。 - sepupic

@sepupic，是的，正如我一开始所说的那样，ISNUMERIC不是最好的函数。 - Vladimir Baranov

如果你遇到','的问题，只需添加如下条件：SELECT CASE WHEN ISNUMERIC(v) = 1 and v not like '%,%' THEN CAST(v AS float) ELSE NULL END AS new_v FROM @T; - sepupic

@Vladimir Baranov 我们现在正在尝试捕捉引起错误的字符。如果我们很幸运，它只是一个逗号，我们将通过添加像我上面评论中的条件来修复它。 - sepupic

@sepupic，我发现这个问题之前已经有人回答过了。我加了一个链接。 - Vladimir Baranov

Answer 2

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);

- vitalygolub

Answer 3

这取决于您的varchar列中的值

对于像'.'和'-'这样的值，ISNUMBER()将返回1，但是当您CAST为FLOAT时会失败

对于像'3D2'，'1e2'这样的值，ISNUMBER()将返回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

- EricZ

---

如何在case语句中防止非数字值？此外，我应该使用LTRIM（RTRIM（Value））吗？ - espresso_coffee

通过你提供的语句，每一行hs_td2列中的所有值都是NULL。 - espresso_coffee

看起来你有空格。你可能需要用 LTRIM(RTRIM(hs_td2)) 替换 hs_td2。 - EricZ