SQL: easy way to cast/parse a varchar to an int or Zero (not NULL)

Question

I want to cast a varchar value of about 11 or 12 characters into an integer value and do some math with it.

If parsing the varchar is not possible, the return value should be 0 (zero) in ANY case. Not null, not an error.

Here's what I tried:

SELECT CAST(NULL AS INT) -- NULL
SELECT CAST('' AS INT)   -- 0
SELECT CAST('42' AS INT) -- 42
SELECT CAST('abc' AS INT) -- Conversion failed when converting the varchar value 'abc' to data type int.

SELECT TRY_PARSE(NULL AS INT) -- Argument data type NULL is invalid for argument 1 of parse function.
SELECT TRY_PARSE('' AS INT)   -- 0
SELECT TRY_PARSE('42' AS INT) -- 42
SELECT TRY_PARSE('abc' AS INT) -- NULL

SELECT TRY_PARSE(ISNULL(NULL, '0') AS INT) -- 0
SELECT TRY_PARSE(ISNULL(NULL, '') AS INT)   -- NULL
SELECT TRY_PARSE(ISNULL(NULL, '42') AS INT) -- 42
SELECT TRY_PARSE(ISNULL(NULL, 'abc') AS INT) -- NULL

The expected output would be

'0' --> 0
'' --> 0
'42' --> 0 42
'abc' --> 0

This works, but hey, this is reeeealy ugly:

SELECT ISNULL(TRY_PARSE(ISNULL(NULL, '0') AS INT), 0) -- 0
SELECT ISNULL(TRY_PARSE(ISNULL(NULL, '') AS INT), 0) -- 0 
SELECT ISNULL(TRY_PARSE(ISNULL(NULL, '42') AS INT), 0) -- 42 
SELECT ISNULL(TRY_PARSE(ISNULL(NULL, 'abc') AS INT), 0) -- 0

Is there a simple way without using exceeding numbers of wrapped method calls?

Note: SQL Server cast varchar to int does not take into account the various error situations

Answer 1

You could use try_convert() with coalesce() function to avoid error message

select coalesce(try_convert(int, null), 0) -- 0   
select coalesce(try_convert(int, ''), 0) -- 0   
select coalesce(try_convert(int, '42'), 0) -- 42   
select coalesce(try_convert(int, 'abs'), 0) -- 0   

Answer 2

No need for the two ISNULL()'s

SELECT isnull(try_convert(int,null),0)   -- 0
SELECT isnull(try_convert(int,'0'),0)    -- 0
SELECT isnull(try_convert(int,''),0)     -- 0
SELECT isnull(try_convert(int,'42'),0)   -- 42
SELECT isnull(try_convert(int,'abc'),0)  -- 0