isnumeric() ?

Question

isnumeric() ?

SQL_path

SSCrazy

Points: 2810
More actions
June 7, 2012 at 11:07 am

#392461

Hi,
I have to convert varchar result values in my table to decimal for charting purposes, and I filter the values as isnumeric(value)=1
but i keep getting the error
Arithmetic overflow error converting numeric to data type numeric. Any ideas to fix that ?
thanks

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

There may be data with symbols like $ or e or . . ISNUMERIC is not a 100% solution for finding is the values are INTERGER-convertible.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

IsNumeric should actually be called something like CanBeConvertedToSomeNumericdataType

IsNumeric('123,234') returns 1, but it can't be converted to numeric, only money

Try casting to float if the accuracy isn't a problem, it's a little more flexible than numeric. Other than that, see what that column contains other than pure numbers and post some examples here

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL_path SSCrazy Points: 2810 More actions · Answer 3

SQL_path

SSCrazy

Points: 2810

June 7, 2012 at 12:36 pm

#1498355

Any ideas?? thanks

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

SQL_path (6/7/2012)
Any ideas?? thanks

Well, since we can't see from here what you see there, not really. Maybe if you provided us with the DDL for the table, some sample data, your query, we might be able to help you better.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

SQL_path (6/7/2012)
Any ideas?? thanks

Yes

GilaMonster (6/7/2012)
Try casting to float if the accuracy isn't a problem, it's a little more flexible than numeric. Other than that, see what that column contains other than pure numbers and post some examples here

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

GSquared SSC Guru Points: 260824 More actions · Answer 6

If you have IsNumeric in the Where clause, you can get some odd behavior, where it tries to convert out-of-range or invalid values to your numeric format, then filter them, instead of the other way around. The usual way to get around that is to use IsNumeric() = 1 to dump the valid rows into a temp table, then do the rest of your process from there.

But also keep in mind, as Gail mentioned, IsNumeric isn't always the best bet for determining the validity of numeric data. For example, you can't convert "1D3" to an integer, but IsNumeric('1D3') will return a 1 because it can be converted to a scientific-notation version of 1,000.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Dwain Camps SSC Guru Points: 86948 More actions · Answer 7

You might want to select out all the rows based on a PATINDEX that allows only numerics. Then you can examine what's actually in the data and what might be causing you problems.

Maybe you can then do the conversion after doing some simple REPLACEments or something.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

GSquared SSC Guru Points: 260824 More actions · Answer 8

dwain.c (6/8/2012)
You might want to select out all the rows based on a PATINDEX that allows only numerics. Then you can examine what's actually in the data and what might be causing you problems.
Maybe you can then do the conversion after doing some simple REPLACEments or something.

The trick with using PatIndex for that kind of thing, is that you might end up with contradictory rules. If, for example, you only allow characters 0-9, then 1.5 won't show as a number, but if you allow 0-9 and ".", then 1.1.1900 shows as a "number". If your data might have European sources, then you have to watch out for the inversion of commas and periods, compared to US numbers; 1.000 = 1-thousand in some notations, and 1 in others, and 1.000.000 is 1-million in EU notation, while 1,000,000 is in the US. 1,000.00 is a legit number in one notation, and not in the other, and 1.000,00 is the same number or error.

So PatIndex, which just checks for existence of allowed characters, can be easily fooled into allowing false positives, or blocking false negatives.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 9

SQL_path (6/7/2012)
Any ideas?? thanks

Yes... to repeat what the others have said but from the diaphram, please see the following article which explains a way to find postive integer values sans any possible $ signs, etc., and why ISNUMERIC() should never be used as an "IsAllDigits" function.

http://www.sqlservercentral.com/articles/IsNumeric/71512/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQL_path SSCrazy Points: 2810 More actions · Answer 10

Thank you Everyone. Greatly Appreciate it. Learned a lot.

Thanks.

Paul Song SSC Journeyman Points: 84 More actions · Answer 11

I use this trick - adding e0

For example,

Select ISNUMERIC('10,000')

--> 1

Select Convert(float, '10,000')

-->

Msg 8114, Level 16, State 5, Line 2

Error converting data type varchar to float.

So adding e0

Select ISNUMERIC('10,000' + 'e0')

--> 0

Select ISNUMERIC('10000' + 'e0')

--> 1

But it is not 100%. It doesn't work if this alrady contain e?. 🙂

Jan Van der Eecken SSCrazy Eights Points: 9029 More actions · Answer 12

Maybe use

FROM WhereEver WHERE CHARINDEX ( 'E0', MyColumn ) = 0

to not catch those guys who already have E0 in them? Not foolproof either. And you don't want to look for just an 'E'

Regards,

Jan

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]