SQL Server 2008 - 如何在 SQL 中查找将 varchar 转换为 int 失败中的故障

Question

我有一列varchar(50)，我想使用以下代码行将其转换为int：

ALTER TABLE [Table].[dbo].[example]
ALTER COLUMN XYZ int

我盯着第 XYZ 列的内容，它们看起来像永远不会超过 9999 的整数。

我熟悉Visual Studio中的调试器，并且知道如何逐步执行代码。 但是，当我尝试使用 SQL Server Mgmt. Studio 调试器时，我不知道如何归零并修复引发此错误的字段：

将 varchar 值"XYZ"转换为数据类型 int 时转换失败。

查询运行了大约 1 分钟，似乎可以工作，但随后弹出了上述错误。

Answer 1

您可以使用正则表达式来确定哪些字段无法正确转换。

SELECT * 
FROM example 
WHERE xyz LIKE '%[^0-9]%'

返回的记录是当您更改表时不会正确转换的记录。 存储在 varchar 字段中的小数也不会转换为整数。

考虑：

select CAST('1.1' as int) -- 这将返回错误。

祝你好运。

Answer 2

您可以使用IsNumeric()查找列中没有int值的行：

select *
from example
where isnumeric(xyz) <> 1

请参阅带有演示的 SQL 小提琴

您还可以使用 CASE 语句：

select 
  case 
    when isnumeric(xyz) <> 1 
    then xyz end
from example
where isnumeric(xyz) <> 1

请参阅带有演示的 SQL 小提琴

如果要查找可能包含句点的记录，以便它们不是int值，则可以使用：

select  xyz
from example
where isnumeric(xyz) <> 1
  or charindex('.', xyz) <> 0

请参阅 SQL 摆弄演示。确定这些记录后，您可以确定如何继续ALTER TABLE

Answer 3

确保可以强制转换值的唯一方法是在 SQL Server 2012 中使用Try_Cast/Try_Parse函数或在早期 SQL Server 版本中使用 Try Catch 模式。

Isnumeric函数在这里几乎没有任何价值，因为如您所见，如果您运行下面的语句，它将为显示的值返回 1(true(。但是，该值不能强制转换或转换为任何数值数据类型。

使用正则表达式"NOT LIKE '%[^0-9]%' "来测试您的值将消除字符串中非数字字符的问题。 但是，它不能保证可以将值强制转换为给定的数据类型。例如，"999999999"是一个有效的整数，但如果将其转换为smallint，则会导致溢出错误。

Ex1
Select Isnumeric('$123,456.00')
--Select Cast( '$123,456.00' as int)
--Select Cast( '$123,456.00' as numeric(6,2))
--Select Cast( '$123,456.00' as decimal(6,2))
--Select Convert(int, '$123,456.00')
--Select Convert(numeric(6,2), '$123,456.00')
Select Convert(decimal(6,2), '$123,456.00')