SQL Server 2005 将 VARCHAR 转换为 INT 但默认为无效类型 [英] SQL Server 2005 Convert VARCHAR to INT but default on invalid type

问题描述

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

I have a varchar(100) column in a table that contains a mix of integers (as strings) and non-integer strings. E.g.

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

我如何写一个表达式，例如总结所有有效整数的值?如果我要尝试:

How can I write an expression to, e.g. sum up all the values that are valid integers? If I were to try:

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

在将 varchar 值Pyramids"转换为数据类型 int 时，我会收到 Conversion failed. 错误.

I would receive a Conversion failed when converting the varchar value 'Pyramids' to data type int. error.

是否有我可以在表达式中使用的测试，就像 ISNULL() 函数一样，允许我在字段不是数字时指定默认值?

Is there a test I can use in my expression, much like the ISNULL() function, that permits me to specify a default value if the field is not a number?

推荐答案

试试这个:

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

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

The CASE should check whether dimension is numeric - if so, return that value. If it's not numeric, return a default value (here: 0)

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

Is you need to query that quite frequently, you could also add a persisted, computed column to your table which encapsulates this computation and stores the value. This way, when summing and so on, you're not always re-computing the value:

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

现在，您可以从 mytable 中选择维度、数值并获取这两个值，而无需执行任何计算.

Now, you can SELECT dimension, numericvalue FROM mytable and get both values without any computation having to be executed.

这篇关于SQL Server 2005 将 VARCHAR 转换为 INT 但默认为无效类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！