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

Question

我正在尝试将我的数据库字段从 VARCHAR(4) 转换为 FLOAT。这些字段中的某些值可能不是数字，因为这些字段之前没有任何验证。我的主要目标是将任何 integer 或 decimal 值转换为 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

第一步我修剪值，然后检查它是否为数字，然后转换为 float ，否则设置为 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() 是否是我应该使用的最佳函数以及为什么我的代码会产生上面列出的错误消息？

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

Answer 1

不，ISNUMERIC 不是最好的函数。

本质上，这个问题之前已经被问过，虽然不是在这个措辞中:

Try_Convert for SQL Server 2008 R2

投票最多的答案建议转换为 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'),
('$');

查询失败:

Error converting data type varchar to float.

ISNUMERIC 可能不会提示其他特殊字符及其组合。这就是为什么我最初说总的来说，ISNUMERIC 不是最好的函数。

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