January 3, 2007 at 3:10 am
Hi
Background: I have Invoice Data with Invoice Number in a form of alphanumerics (nvarchar). Basically I need to identify consecutive Invoice numbers so I have stripped out any spaces or alpha characters and punctuation. (therefore i Need the Invoice Number in numeric to run calcuation i.e. Invoice_No +1....
After cleaning I have an Invoice number with purely numerics (I run a isnumeric(invoice_No) = 0 check to ensure this).
Now when I try to convert this Invoice Number to Numeric using (ALTER TABLE Invoice_TABLE Alter column Invoice_No numeric)
I get the following error:
"Arithmetic overflow error converting nvarchar to data type numeric"
The max length of the Invoice Number field is 20. There are no nulls and I have some values of 0.
Can anybody offer advice on how I can convert???
Thanks in Advance.
January 3, 2007 at 3:51 am
Hi,
a few pointers...
- ISNUMERIC does not check for "numbers only" properly. Certain values are considered numeric, althought they contain letters and other signs (like +, - and a few more). Make a search for ISNUMERIC on these forums if you're interested - we have discussed it several times. Some letters are part of scientific notation (3E24) or have other reason to be considered as a possible part of a number.
For example, all these are considered numeric - result is 1.
SELECT ISNUMERIC('+002')
SELECT ISNUMERIC('-3E265')
SELECT ISNUMERIC('6D78')
If you want to allow numbers only, the reliable way is to use LIKE comparision:WHERE Invoice_No LIKE '%[^0-9]%' should bring up all invoices that have other characters than numbers included.
- if you want to have the invoice number stored as a number, why not use INTEGER? I don't see why you would need decimal places here... I suppose 9 numbers (2,147,483,647 is maximum value for INT) for invoice number should be enough? And even if not, then I suggest you should consider BIGINT, not NUMERIC.
I think the conversion error could be due to some missed characters in the invoice number (trying to convert some "exponential" number could easily result in overflow). Try again after you check it with LIKE and lets us know whether it helped or not.
January 3, 2007 at 4:44 am
Hi I managed to sort this.
Basically as the default precision for numeric is (18,0) I increased it to (20,0) due to the high values in my data. That solved it.
I did try BIGINT but it produced an error. I was still able to convert to numeric.
Thanks for your help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply