Q
Problem solve Get help with specific problems with your technologies, process and projects.

Error converting varchar to numeric

I am using DTS to take a text file and create a one column table (varchar). From here I am parsing (using substring commands) the one column table into several tables based on a criteria - and that's working fine. Until I try to create a numeric column in any of the tables. I'm receiving "Error converting varchar to numeric". Is there a workaround for this?

What is the structure of the table to start with and what is the DDL you are issuing to alter the table?

Editor's note: In resonse to Michael's request for more information, the user replied to say that she had resolved the issue. Here is what she said:

I have resolved the issue, I will try to explain so others may benefit...

The text file had fields created in COBOL as 9(7).9(4)-. The decimal and sign were hard coded. Every time I tried to create the table with a numeric column [numeric](13,4) null, and run the following code I got an error.

 

 insert into Table_Name select substring(onecolumntable,1,13) as cost,

I found out that SQL Server 2000 does not like the hard coded sign on the right of the expression so - this is what I did...

 

 insert into Table_Name select substring(onecolumntable,13,1) + substring(onecolumntable,1,12) as Cost

Dig Deeper on Microsoft SQL Server Installation

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close