SQL：如何在查询中使用案例和转换？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5642165/
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: How use case and cast in a query?
提问by Valter Silva
I want to cast VARCHAR to INT, but in my table i have some value like '???' then SQL Server launch this expcetion :
我想将 VARCHAR 转换为 INT，但在我的表中，我有一些像“???”这样的值 然后 SQL Server 启动这个 expcetion ：
Conversion failed when converting the varchar value '????' to data type int.
Severity 16
I could convert this '???' to NULL, that's no problem, but how do that ?
我可以转换这个'???' NULL，那没问题，但是怎么做呢？
I'm trying to do something like this:
我正在尝试做这样的事情：
INSERT INTO labbd11..movie(title, year)
SELECT movies.title,
CASE movies.mvyear IS '????' THEN NULL ELSE CAST (movies.mvyear AS INT)
FROM disciplinabd..movies
But nothing works ..
但没有任何作用..
Any ideas guys ?
有什么想法吗？
回答by Conrad Frix
You might just want to solve this in general and deal with any non-int value the same way
您可能只想解决这个问题并以相同的方式处理任何非 int 值
INSERT INTO labbd11..movie(title, year)
SELECT movies.title,
CASE WHEN IsNumeric(movies.mvyear+ '.0e0') <> 1 THEN NULL
ELSE CAST (movies.mvyear AS INT) END
FROM disciplinabd..movies
See this question
看到这个问题
回答by Justin Cave
I believe you would want something like
我相信你会想要类似的东西
INSERT INTO labbd11..movie(title, year)
SELECT movies.title,
CAST( CASE movies.mvyear
WHEN '????' THEN NULL
ELSE movies.mvyear
END AS INT)
FROM disciplinabd..movies
You want your CASE statement to return a VARCHAR (either the MVYEARor NULL) and then you want the CAST to operate on the result of the CASE.
您希望 CASE 语句返回一个 VARCHAR（MVYEAR或 NULL），然后您希望 CAST 对 CASE 的结果进行操作。
回答by Nitin Midha
INSERT INTO labbd11..movie(title, year)
SELECT movies.title,
CASE WHEN movies.mvyear = '????' THEN NULL
ELSE CAST (movies.mvyear AS INT) END
FROM disciplinabd..movies
回答by amit_g
INSERT INTO labbd11..movie(title, year)
SELECT
movies.title,
CAST(CASE WHEN movies.mvyear = '????' THEN NULL ELSE movies.mvyear END AS INT)
FROM
disciplinabd..movies
回答by Martin Smith
You can also use:
您还可以使用：
CAST(NULLIF(movies.mvyear,'????') AS INT)
