By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,569 Members | 1,386 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,569 IT Pros & Developers. It's quick & easy.

how to convert varchar to numeric ?

P: n/a
meh

I have imported data from excel file. When data came to SQL table, the type
of AMOUNT column was varchar. I tried to convert and cast amount type of
amount column to number type but it does not allow me to convert.

What is the best way of importing data into SQL and type stays the same as
it was in excel file ?

Or anyone has any better solution, please let me.

Thanks.
Jul 20 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Meh,

You need a 2nd column, temporarily at least:

Update MyTable
set fltAmount = cast( vchAmount as float )
where isnumeric( vchAmount ) = 1

Then check the ones that dont update for garbage.

Regards
AJ

"meh" <me**@hotmail.com> wrote in message news:w1******************@nwrddc01.gnilink.net...

I have imported data from excel file. When data came to SQL table, the type
of AMOUNT column was varchar. I tried to convert and cast amount type of
amount column to number type but it does not allow me to convert.

What is the best way of importing data into SQL and type stays the same as
it was in excel file ?

Or anyone has any better solution, please let me.

Thanks.

Jul 20 '05 #2

P: n/a
FYI, that will sometimes fail.

For example:

declare @s varchar(25)
set @s = '-'
select cast(@s as float) where isnumeric(@s) = 1

How try with integer instead of float.

Bye,
Delbert Glass

"Andrew John" <aj@DELETEmistrose.com> wrote in message
news:3f********@duster.adelaide.on.net...
Meh,

You need a 2nd column, temporarily at least:

Update MyTable
set fltAmount = cast( vchAmount as float )
where isnumeric( vchAmount ) = 1

Then check the ones that dont update for garbage.

Regards
AJ

"meh" <me**@hotmail.com> wrote in message

news:w1******************@nwrddc01.gnilink.net...

I have imported data from excel file. When data came to SQL table, the type of AMOUNT column was varchar. I tried to convert and cast amount type of amount column to number type but it does not allow me to convert.

What is the best way of importing data into SQL and type stays the same as it was in excel file ?

Or anyone has any better solution, please let me.

Thanks.


Jul 20 '05 #3

P: n/a
You could create a new table, with the correct datatypes. Then use a select
into query to add the data collected from the import. Use the convert()
functionality to make the varchars integers.

"meh" <me**@hotmail.com> wrote in message
news:w1******************@nwrddc01.gnilink.net...

I have imported data from excel file. When data came to SQL table, the type of AMOUNT column was varchar. I tried to convert and cast amount type of
amount column to number type but it does not allow me to convert.

What is the best way of importing data into SQL and type stays the same as
it was in excel file ?

Or anyone has any better solution, please let me.

Thanks.

Jul 20 '05 #4

P: n/a
"Tim Hinkel" <th*****@orcapack.com> wrote in message
news:u40mb.13642$e01.26060@attbi_s02...
You could create a new table, with the correct datatypes. Then use a select into query to add the data collected from the import. Use the convert()
functionality to make the varchars integers.

"meh" <me**@hotmail.com> wrote in message
news:w1******************@nwrddc01.gnilink.net...

I have imported data from excel file. When data came to SQL table, the

type
of AMOUNT column was varchar. I tried to convert and cast amount type of amount column to number type but it does not allow me to convert.

What is the best way of importing data into SQL and type stays the same as it was in excel file ?

Or anyone has any better solution, please let me.

Thanks.


You could create a DTS package that coerced the data into the correct format
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.530 / Virus Database: 325 - Release Date: 22/10/2003
Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.