SQL：如何在查询中使用用例和转换？

Question

我想将 VARCHAR 转换为 INT，但在我的表中我有一些值，例如“???”然后 SQL Server 启动这个expcetion：

Conversion failed when converting the varchar value '????' to data type int.
Severity 16

我可以转换这个'???'为NULL，这没问题，但是怎么做呢？

我正在尝试做这样的事情：

INSERT INTO labbd11..movie(title, year)
SELECT movies.title, 
       CASE movies.mvyear IS '????' THEN NULL ELSE CAST (movies.mvyear AS INT)
FROM disciplinabd..movies

但是没有任何效果..

有什么想法吗？

Answer 1

您可能只是想一般性地解决这个问题并以相同的方式处理任何非 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

请参阅此问题

Answer 2

我相信您会想要类似的内容

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

您希望您的 CASE 语句返回 VARCHAR（MVYEAR 或 NULL），然后您希望 CAST 对 CASE 的结果进行操作。

Answer 3

    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

Answer 4

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

Answer 5

您还可以使用：

CAST(NULLIF(movies.mvyear,'????') AS INT)