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.

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 Getting error on converting nvarchar to numeric

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 Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

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).
Go to Top of Page

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 Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

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 error
try this
select convert(numeric(10,2), '123.456')


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

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
Go to Top of Page

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]

Go to Top of Page

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
Go to Top of Page

cda
Starting Member

3 Posts

Posted - 2008-07-25 : 18:43:25
nevermind, found my own answer...
where isnumeric(QUANTITY)=0

:)
Go to Top of Page

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 reliable

http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html
Go to Top of Page

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.
Go to Top of Page

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.
FieldName
012564325
123456789
052864123

And, Dont want to truncate starting zero while convertion.
Any,Help would be appeciated.
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -