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

Question

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?)

Answer 1

Use ISNUMERIC

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

Answer 2

Empty string will convert to zero for float and int types, but not decimal. (And converts to 01 Jan 1900 for datetimes = zero). I don't know why.. it just is...

If you need decimal(18,0), use bigint instead. Or cast via float first

ISNUMERIC will accept "-" and "." and "1.2E3" as a number but this will all fail to convert to decimal.

Answer 3

ISNUMERIC doesn't alway work as you might expect: in particular it returns True for some values that can't subsequently be converted to numeric.

This article describes the issue and suggests how to work around it with UDFs.