SQL: How use case and cast in a query?

Question

I want to cast VARCHAR to INT, but in my table i have some value like '???' then SQL Server launch this 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 ?

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 ?

Answer 1

You might just want to solve this in general and deal with any non-int value the same way

 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