SQL: How use case and cast in a query?
85,645
Solution 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
Solution 2
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.
Author by
Valter Silva
Updated on July 18, 2022Comments
-
Valter Silva almost 2 years
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 ?
-
Valter Silva about 13 years@Thanks so much for you help guys, you guys rock!