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.

but

PRINT CONVERT(INT, '')

creates 0 without errors ...

Question: Is there any SQL Server flag for this, or will I need to do case statements for each varchar to numerical conversion? (besides 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

An empty string converts to zero for types float and int, but not decimal. (And it is converted on January 01, 1900 for datetimes = zero). I don’t know why .. it's just ...

If you need decimal (18.0), use bigint instead. Or listing with float first

ISNUMERIC will accept - and . and 1.2E3 as a number, but everyone will not be able to convert them to decimal.

Answer 3

ISNUMERIC does not work as you might expect: in particular, it returns True for some values ​​that subsequently cannot be converted to numeric.

This article describes the problem and suggests how to work with it using UDF.