Error converting data type varchar to numeric

Question

Post reply

Error converting data type varchar to numeric

Viewing 5 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

NoviceJatt SSCommitted Points: 1671 More actions · Answer 1

Hi

Yes,working with CSV files is really hard. Lot of manual formating has to be done.

SELECT CAST ('6.68E+07' AS FLOAT)

Do I have to do this for every such value? I have a lot of these.

How would this be included into my above query?

Thanks

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 2

you don't need to do that for all manually, use an update statement.. to update all values with 'E' in it using a case statement with Patindex

NoviceJatt SSCommitted Points: 1671 More actions · Answer 3

Hi

Divyanth,Can you update my query with what you are saying ?

Thanks

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 4

I would suggest using a SELECT that CELKO suggests as updating is always expensive..But if you still want to below is the code

UPDATE TableName

SET ColName = CAST (CAST(ColName AS FLOAT) AS DECIMAL(15,8))

WHERE PATINDEX('%E%',ColName) <> 0

NoviceJatt SSCommitted Points: 1671 More actions · Answer 5

Hi,

Thankyou guys, great help.

@CELKO .... I can't BULK INSERT from a CSV file into the table if I use the right datatypes. So I have to do all the manipulations in Views. I can only import data if I use VARCHAR. Also for now I don't want to use any ETL tools.

@divyanth .... Thanks for the query .... but CELKO's query works for me as I don't want to change the table coz if I do, I would not be able to insert more data into the table from other CSV files.

Thanks.