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 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.

Answer 4

Old question maybe but I ran into this when suddenly my varchar field was sometimes empty when I also had to convert 1,000.50 to 1000,50

My trick is to use:

PRINT CONVERT(NUMERIC(18,0), '0' + replace(replace(myvarchar,',',''),'.',','))

The preleading zero will be ignored during conversion unless myvarchar is empty.