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

Shoifet

I don't think it's unreliable, it's just not a complete test. It says that the value converts to one of the numeric types successfully, so I added conditions to test for at least one number, no dollar sign, no scientific notation, etc. – Shoifet

How do you know it's unreliable? You had to ask. When shown different (and working) techniques with derived tables and CASE you said you don't want to rely on them. Downvote stands in case other folk read this. – Raffish

case when @a not like '%[^0-9.]%' is very close but without isnumeric(), it would accept things like '123.45.67' – Shoifet

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

Prandial

I guess I could refactor and put all these concerns in in-line functions. The only bad thing about case statements in a select statement is making the select statement three pages long. – Shoifet

The above won't work for all input strings, for example try @a='-' or @a='$'. See the article referenced in my answer. – Zurek

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.

Raffish

My question needs to be better worded. I meant to ask how to reliably test for a number in a string. I see you didn't like the isnumeric with good reason, but I don't see how casting it to bigint or float first would determine it's a valid number without errors either. From what I understand, casting the string to bigint would produce a incorrect zero value or fail the script. Casting to float seems to always fail regardless. – Shoifet

@Dr. Zim: in that case, see these #1623378 and #3760855 – Raffish

I haven't seen a way to do it without isnumeric with the exception of not myField like '%[^0-9]%' and LEN(myField) < 9 (but this doesn't work for decimal.) However, doing isnumeric() and not (check for things it misses) worked for me (which I commented on in the answer) thus why I marked it. – Shoifet

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.

Zurek

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.

Disadvantage