SQL Server 2005 将 VARCHAR 转换为 INT 但默认为无效类型

Question

我在包含整数(作为字符串)和非整数字符串的混合表中有一个 varchar(100) 列。例如。

| dimension varchar(100) |
| '5'                    |
| '17'                   |
| '3'                    |
| 'Pyramids'             |
| 'Western Bypass'       |
| '15'                   |

我怎样才能写一个表达式，例如总结所有有效整数的值？如果我要尝试:

-- should return 5 + 17 + 3 + 15 = 40
SELECT
    SUM( CONVERT( INT, dimension ) )
FROM
    mytable

我会收到 Conversion failed when converting the varchar value 'Pyramids' to data type int.错误。

是否有我可以在表达式中使用的测试，很像 ISNULL()函数，如果该字段不是数字，它允许我指定一个默认值吗？

Answer 1

尝试这个:

SELECT
    SUM(CASE ISNUMERIC(dimension) 
           WHEN 1 THEN CONVERT( INT, dimension ) 
           ELSE 0  
        END)
FROM
    mytable

CASE应该检查是否 dimension是数字 - 如果是，则返回该值。如果它不是数字，则返回一个默认值(此处:0)

您是否需要经常查询，您还可以向表中添加一个持久化的计算列，该列封装了此计算并存储了值。这样，在求和等时，您并不总是重新计算值:

ALTER TABLE mytable
  ADD NumericValue AS CASE ISNUMERIC(dimension) 
      WHEN 1 THEN CONVERT( INT, dimension ) ELSE 0 END PERSISTED

现在，您可以 SELECT dimension, numericvalue FROM mytable并获得两个值而无需执行任何计算。