CODEWITHSUNDEEP
sql-server
Gargoyle
Gargoyle

Reputation: 10375

SQL CAST with ISNULL results in conversionfailure

I've got a nullable column of numbers, which is unfortunately declared as a varchar in the database, and so I want to convert the NULL to 0. Doing this accomplishes that.

select ISNULL(col, '0')  from table;

So now there are no null possibilities, and then I want to convert the column to an actual int value, so I tried to wrap that.

select CAST(ISNULL(col, '0') AS INT) from table

When that runs I get a conversion error, as shown below. I don't understand why, or how to get around this:

Conversion failed when converting the varchar value 'NULL' to data type int.

Upvotes: 1

Views: 10424

Answers (1)

Irfan
Irfan

Reputation: 695

Try like this: Take your ISNULL statement outside

SELECT ISNULL(TRY_CAST(col AS INT),0) AS [col]
FROM SAMPLE_TAB;

If this does not help, then please comment your SQL version.

Upvotes: 2

Related Questions