sql-server - 查找表中值无法转换为 int 的行

Question

我需要一种方法来检测 MSSQL 表中包含无法转换为 int 的 varchar 条目的行。

我正在开发的系统在一个地方使用可编辑的 varchar 字段，并将其连接到另一个地方的整数列。如果 varchar 字段中包含无效数字(空白、带有 alpha 或符号，或者是超过 21 亿的数字(最大 int 大小))，则另一个查询会失败，表示值 xyz 溢出了 int 列，或者可能不被转换。

我想出了以下部分解决方案来查找违规记录:

select g.id, g.membernumber from groups g
left join secondary_groups sg on sg.id=g.id 
where convert(bigint, g.membernumber) > 2147483647 
or isnumeric(g.membernumber) = 0

这对于大多数情况都适用，但后来我意识到，如果您的 varchar 值超过“bigint”，它可能会失败。是否有更通用的方法来定位这些类型的记录，而不是使用强制转换/转换？如果有一个“IsInt()”的内置方法那就太棒了，但是可惜......

Answer 1

从 SQL Server 2012 开始，您可以使用 TRY_CAST当无法执行转换时返回 NULL 的表达式:

select * from table
where TRY_CAST(Value as int) is null

请注意，TRY_CAST 使用与 CAST 相同的转换规则，例如它将空白字符串转换为 0。这在您的情况下不是问题，但在 SQL 外部使用转换结果时应考虑。

还有类似的TRY_CONVERT表达式与 CONVERT 具有相同的语法，但需要将数据库兼容性级别更改为 120 或更高，而 TRY_CAST 也适用于 100。