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 ?

score 10 · Accepted Answer · edited May 23 '17 at 12:13

10

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

edited May 23 '17 at 12:13

Community

1
1

answered Apr 12 '11 at 22:13

Conrad Frix

51,984
12
96
155

@Thanks so much for you help guys, you guys rock! – Valter Silva Apr 12 '11 at 22:31

score 5 · Answer 2 · answered Apr 12 '11 at 22:10

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 MVYEAR or NULL) and then you want the CAST to operate on the result of the CASE.

score 0 · Answer 3 · answered Apr 12 '11 at 22:10

0

    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

answered Apr 12 '11 at 22:10

Nitin Midha

2,258
20
22

score 0 · Answer 4 · answered Apr 12 '11 at 22:12

0

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

answered Apr 12 '11 at 22:12

amit_g

30,880
8
61
118

score 0 · Answer 5 · edited Aug 12 '12 at 07:05

0

You can also use:

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

edited Aug 12 '12 at 07:05

answered Apr 12 '11 at 22:17

Martin Smith

438,706
87
741
845

SQL: How use case and cast in a query?

5 Answers5