Author |
Topic |
Babaksql
Starting Member
32 Posts |
Posted - 2008-03-10 : 09:31:14
|
Hi,I used to work with Sql 2000 and I could easily import a text file into my database and then convert the fields (all nvarchar) to appropriate data types. However, this procedure does not work with Sql 2005.After I import a text file to Sql 2005 and when I convert nvarchar to numeric, I get "Error converting data type nvarchar to numeric". Could you please let me know how can I fix this issue. Thanks in advance for your reply. |
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-03-10 : 09:32:47
|
You have to make sure that there is no non-numeric value in nvarchar data.Select * from table where col not like '%[0-9]%' Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
Babaksql
Starting Member
32 Posts |
Posted - 2008-03-10 : 09:43:09
|
The field that I get error for that has numbers like "xxx.yyy" so character "." is the cause of error. Could you please tell how can I remove it and then how can I convert it to numeric (a, b). |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-03-10 : 09:48:28
|
Character "." should not cause problem while converting. Is there any space character before or after "." or any other non-numeric character?Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-03-10 : 09:52:30
|
quote: Originally posted by Babaksql The field that I get error for that has numbers like "xxx.yyy" so character "." is the cause of error. Could you please tell how can I remove it and then how can I convert it to numeric (a, b).
This would not cause the conversion errortry thisselect convert(numeric(10,2), '123.456') KH[spoiler]Time is always against us[/spoiler] |
|
|
Babaksql
Starting Member
32 Posts |
Posted - 2008-03-10 : 09:57:29
|
I'm 90% confident that there is no non-numeric character. The 10% is the possibility of having space character.khtan could you please explain your solution a bit more. Thanks |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-03-10 : 10:20:17
|
it is not a solution. Just to illustrate that string with xxx.yyy can be converted to numeric KH[spoiler]Time is always against us[/spoiler] |
|
|
cda
Starting Member
3 Posts |
Posted - 2008-07-25 : 18:34:55
|
if i'm not mistaken, this statement:> Select * from table where col not like '%[0-9]%'will only match a string in which there is NO numeric character. in other words, a string such as "9abc000" will not be matched, but this is clearly not a numeric string and would cause the error in question. does anyone know a way to select on numeric strings, something like [0-9]*[\.]?[0-9]+ in perl?thanks,-cda |
|
|
cda
Starting Member
3 Posts |
Posted - 2008-07-25 : 18:43:25
|
nevermind, found my own answer...where isnumeric(QUANTITY)=0:) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-26 : 11:51:57
|
quote: Originally posted by cda nevermind, found my own answer...where isnumeric(QUANTITY)=0:)
but its also not fully reliablehttp://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html |
|
|
cda
Starting Member
3 Posts |
Posted - 2008-07-29 : 10:24:52
|
hmm, yes, in fact just got bitten by this... apparently the importer can't deal with scientific notation...i solved it by getting the input data in a different format. |
|
|
avni0710
Starting Member
1 Post |
Posted - 2009-02-12 : 01:38:32
|
Hi..I also want to convert nvarchar datatype field to numeric.And,in my table field there is niether space nor characters,but the problem is some data is starting with 0.Eg.FieldName012564325123456789052864123And, Dont want to truncate starting zero while convertion.Any,Help would be appeciated. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-12 : 10:49:06
|
what do you mean by not truncate starting 0? converting to numeric automatically removes left 0's as it has no significance. |
|
|
|