6

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Zachary Scott
  • 20,968
  • 35
  • 123
  • 205
  • 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. – Zachary Scott Sep 27 '11 at 00:44
  • How do you know it's unreliable? You had to *ask*. When [shown different (and working) techniques](http://stackoverflow.com/q/3760355/27535) with derived tables and CASE you said you don't want to rely on them. Downvote stands in case other folk read this. – gbn Sep 27 '11 at 04:37
  • `case when @a not like '%[^0-9.]%'` is very close but without isnumeric(), it would accept things like '123.45.67' – Zachary Scott Sep 30 '11 at 00:23

4 Answers4

6

Use ISNUMERIC 

declare @a varchar(20)
set @a = 'notanumber'
select case when isnumeric(@a) = 0 then 0 else convert(numeric(18,0),@a) end
SqlACID
  • 4,024
  • 20
  • 28
  • 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. – Zachary Scott Nov 27 '09 at 20:42
  • The above won't work for all input strings, for example try @a='-' or @a='$'. See the article referenced in my answer. – Joe Dec 01 '09 at 05:56
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.

gbn
  • 422,506
  • 82
  • 585
  • 676
  • 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. – Zachary Scott Sep 26 '11 at 13:42
  • @Dr. Zim: in that case, see these http://stackoverflow.com/questions/1622878/why-use-select-top-100-percent/1623240#1623240 and http://stackoverflow.com/questions/3760355/how-can-i-query-between-numeric-data-on-a-not-numeric-field/3760397#3760397 – gbn Sep 26 '11 at 19:53
  • 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. – Zachary Scott Sep 30 '11 at 00:14
2

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.

Joe
  • 122,218
  • 32
  • 205
  • 338
-1

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.

Jeremy Caney
  • 7,102
  • 69
  • 48
  • 77
Wim
  • 1