sqlsql-servernumeric-conversion

SQL Server CONVERT(NUMERIC(18,0), '') fails but CONVERT(INT, '') succeeds?

PRINT CONVERT(NUMERIC(18,0), '')

produces Error converting data type varchar to numeric.

However,

PRINT CONVERT(INT, '')

produces 0 without error...

Question: Is there some SQL Server flag for this or will I need to do case statements for every varchar to numeric conversion? (aside from the obvious why?)

Solution

  • Use ISNUMERIC 

    declare @a varchar(20)
set @a = 'notanumber'
select case when isnumeric(@a) = 0 then 0 else convert(numeric(18,0),@a) end