Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dnf999
Constraint Violating Yak Guru
253 Posts |
 Posted - 2007-01-03 : 05:01:17
|
| 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. |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-01-03 : 05:15:41
|
| Don't rely on ISNUMERIC() function to check for validity of numeric data. ISNUMERIC() will give you incorrect results with characters like comma, E and even some control characters like TAB character. Use pattern matching expressions such as LIKE to properly identify numeric data.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
dnf999
Constraint Violating Yak Guru
253 Posts |
Posted - 2007-01-03 : 05:24:15
|
| Hi HarshI have run the query select Invoice_no from Invoice_Table where Invoice_no not like '%[0-9]%' and this returns no values.The only issue I can see is possibly due to some high values i.e. 990007072004, but surely I should still be able to convert all these values to numeric? |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-01-03 : 05:32:16
|
Try this query and see if you are getting any rows:select Invoice_no from Invoice_Table where Invoice_no like '%[^0-9]%' Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
dnf999
Constraint Violating Yak Guru
253 Posts |
Posted - 2007-01-03 : 05:37:32
|
| Hi This query returns no records... |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-01-03 : 05:47:49
|
| What is the maximum value in the table? Also, when you alter the column to Numeric you didn't specify Scale and Precision. So it will be taken as (18,0).Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
dnf999
Constraint Violating Yak Guru
253 Posts |
Posted - 2007-01-03 : 06:38:47
|
| THANK YOU!I just changed the precision to (20,0) and it converted. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
Converting to Numeric problem!