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获取两个值，而不必执行任何计算。