error converting varchar to int data type

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 2000 Forums

Transact-SQL (2000)

error converting varchar to int data type

Author

Topic

speterson
Starting Member

11 Posts

Posted - 2006-01-17 : 21:48:02

I have searched for hours on a solution and have tried everything that I have came across and nothing seems to work. I have a table that contains varchar(50) column in which all data is numeric. I need to convert this data to integer to import via dts to my sql database.

The data in my source table in a field called Customer_Info:

740
2,834
12,630
929
4,461

I have tried convert(int,customer_info), cast(customer_info as int) and many variations of the following:

case
when isnumeric(replace(a.customer_info,',','')) <> 1 then 0
else cast(replace(a.customer_info,',','') as int)
end as customer_info

I have chosen to use the replace funtion to remove the comma as I originally thought that was the problem. All of the suggestions I have found on the internet still return 0 meaning that my data is not numeric. At this point I have looked through each of the 250 record and there is not one value in this customer_info field that is not numeric, yet I still receive only 0 in all fields as opposed to the number. Or, I receive the error converting varchar to int error.

I am at a loss and need an answer as soon as possible so if you have any suggestions I would really appreciate them. I have spent hours on researching this and have nowhere else to turn.

Thank you, in advance, for your immediate assistance!

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-17 : 21:52:54

IsNumeric() returns 0 on ALL of them???

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-18 : 01:28:28

[code]

select num,replace(num,',','') as Number from
(
select '740' num union all
select '2,834' union all
select '12,630' union all
select '929' union all
select '4,461'
) T where isnumeric(num)=1[/code]
Read more about IsNumeric
http://aspfaq.com/show.asp?id=2390

Madhivanan

Failing to plan is Planning to fail

speterson
Starting Member

11 Posts

Posted - 2006-01-18 : 09:17:17

I tried creating the IsReallyNumeric function on the link shown and I still am getting all 0 data returned, with or without the replace function included.

I do not understand...

speterson
Starting Member

11 Posts

Posted - 2006-01-18 : 09:22:09

The select statement works on it's own, however I the source data I gave was just a few lines that I have to work with. Is there a way to do something similar that I would have have to type out every value individually. I am trying to create a view that I will be using within a dts package so I am not sure where that select would fit correctly. Plus I don't want to have to type out thousands of numeric values!! Maybe we're getting closer to a solution!!

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-18 : 09:41:57

So it sounds like not ALL of your data is failing. YOu need to use the IsNumeric function to find which records CANNOT be converted to numeric, and then figure out why and fix the problem.

Show us some values that fail the IsNumeric test.

speterson
Starting Member

11 Posts

Posted - 2006-01-18 : 09:48:44

I have scrolled through all 250 records which is a subset to the thousands of records I am trying to convert. All 250 test records contain only numeric values. Here is an example of the data I am trying to convert:

740
2,834
12,630
929
4,461
2,841
3,952
2,441
3,156
3,744
4,605

When I display the field (customer_info) and the isnumeric value I get the following results:

740 0
2,834 1
12,630 1
929 0
4,461 1
2,841 1
3,952 1
2,441 1
3,156 1
3,744 1
4,605 1

This shows me that the values that do not have a delimiter (comma) do not pass the isnumeric function test. Which I don't understand either. Why would the delimiter constitute the value being numeric?

TimS
Posting Yak Master

198 Posts

Posted - 2006-01-18 : 13:54:09

Have you Tried to replace CHAR(13) and CHAR(10)

Tim S

select num,replace(num,',','') as Number from
(
select '740' + CHAR(13) union all
select '2,834' union all
select '12,630' union all
select '929' union all
select '4,461'
) T (num) where isnumeric(num)=1

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-18 : 14:05:14

Tim thinks there are hidden characters in your field, and I'm thinking along those lines too. But CHAR(13) is only one possibility.

Try:
select num, len(num) from yourtable where IsNumeric(Num) = 0
Make sure the '740' string is only three characters, etc...

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-18 : 14:06:17

...and make sure the value wasn't typed in as '74O' by mistake!

shijobaby500
Starting Member

5 Posts

Posted - 2009-12-11 : 02:12:35

http://sqlerrormessages.blogspot.com/2009/12/error-converting-data-type-varchar-to.html

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-11 : 04:08:16

quote:
Originally posted by shijobaby500

http://sqlerrormessages.blogspot.com/2009/12/error-converting-data-type-varchar-to.html

As your old account is locked, you are now posting your replies with new account and that too only your blog referrals

See your old replies
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97300
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=129175
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=95176
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=114733
etc

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources