有多少种方法可以生成将 varchar 转换为不会被 ISNUMERIC() 捕获的数字的错误？

Question

我正在将一堆表加载到SQL Server中，并将它们从varchar转换为特定的数据类型(int，date等(。 一个令人沮丧的是，有多少种不同的方法可以中断从字符串到数字(整数、十进制等(的转换，并且没有一个简单的诊断工具来查找有问题的行(除了ISNUMERIC()它并不总是有效(。

这是我列出的打破转换的方法列表，这些方法不会被ISNUMERIC()捕获。

• 该字符串包含科学记数法(即 3.55E-10(
• 字符串包含一个空白 (''(
• 字符串包含非字母数字符号("$"、"-"、"，"(

以下是我目前用来补偿的内容：

SELECT 
CASE 
WHEN [MyColumn] IN ('','-') THEN NULL    -- deals with blanks
WHEN [MyColumn] LIKE '%E%' THEN CONVERT(DECIMAL(20, 4), CONVERT(FLOAT(53), [MyColumn]))            -- deals with scientific notation
ELSE CAST(REPLACE(REPLACE([MyColumn] , '$', ''), '-', '') AS DECIMAL(20, 4)) 
END [MyColumn]             -- deals with special characters
FROM 
MyTable

还有其他人吗？ 还是诊断的好方法？

Answer 1

不要使用 ISNUMERIC((。如果你在2012+，那么你可以使用TRY_CAST或TRY_CONVERT。

如果您使用的是旧版本，则可以使用如下语法：

SELECT * 
FROM #TableA
WHERE ColA NOT LIKE '%[^0-9]%'

Answer 2

您可以尝试使用LIKE '%[0-9]%'而不是ISNUMERIC()

SELECT col, CASE WHEN col NOT LIKE '%[^0-9]%' and col<>''
THEN 1 
ELSE 0 
END
FROM T

Answer 3

您可以使用NOT LIKE来排除任何不是数字的内容...逗号和句点REPLACE。当然，您可以为要接受的值添加其他嵌套REPLACE函数。

declare @var varchar(64) = '55,5646'
SELECT
CASE 
WHEN replace(replace(@var,'.',''),',','') NOT LIKE '%[^0-9]%' 
THEN 1 
ELSE 0 
END

这允许您接受小数/数字/浮点转换的小数。