TSQL - replace isnumeric = 0

Question

I have a select statement and in that select statement I have a few columns on which I perform basic calculations (e.g. [Col1] * 3.14). However, occasionally I run into non-numeric values and when that happens, the whole stored procedure fails because of one row.

I've thought about using a WHERE ISNUMERIC(Col1) <> 0, but then I would be excluding information in the other columns.

Is there a way in TSQL to somehow replace all stings with NULL or 0??

Answer 1

I prefer Try_Cast:

SELECT
    someValue
    ,TRY_CAST(someValue as int) * 3.14     AS TRY_CAST_to_int
    ,TRY_CAST(someValue as decimal) * 3.14 AS TRY_CAST_to_decimal
    ,IIF(ISNUMERIC(someValue) = 1, someValue, null) * 3.14 as IIF_IS_NUMERIC
FROM (values 
    ( 'asdf'), 
    ( '2' ),
    ( '1.55')
) s(someValue)