SQL Server 2005 将 VARCHAR 转换为 INT 但默认为无效类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2419451/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server 2005 Convert VARCHAR to INT but default on invalid type
提问by PP.
I have a varchar(100) column in a table that contains a mix of integers (as strings) and non-integer strings. E.g.
我在包含整数（作为字符串）和非整数字符串的混合表中有一个 varchar(100) 列。例如
| 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
I would receive a Conversion failed when converting the varchar value 'Pyramids' to data type int.error.
我会收到一个Conversion failed when converting the varchar value 'Pyramids' to data type int.错误。
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?
是否有我可以在我的表达式中使用的测试，就像ISNULL()函数一样，如果字段不是数字，它允许我指定一个默认值？
回答by marc_s
Try this:
尝试这个：
SELECT
SUM(CASE ISNUMERIC(dimension)
WHEN 1 THEN CONVERT( INT, dimension )
ELSE 0
END)
FROM
mytable
The CASE should check whether dimensionis numeric - if so, return that value. If it's not numeric, return a default value (here: 0)
CASE 应该检查是否dimension是数字 - 如果是，则返回该值。如果它不是数字，则返回一个默认值（此处：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
Now, you can SELECT dimension, numericvalue FROM mytableand get both values without any computation having to be executed.
现在，您SELECT dimension, numericvalue FROM mytable无需执行任何计算即可获得这两个值。
回答by Craig Curran
You're going to run into overflow problems if you have a varcharlike 88888888888888888888888
你会，如果你有碰到溢出问题varchar像88888888888888888888888
select
SUM(
CASE
WHEN ISNUMERIC(CheckCol+ '.0e0') = 1 AND
convert(decimal(38,0), CheckCol)
BETWEEN -2147483648 AND 2147483647
THEN 1
ELSE 0
END
)
from Table
