469,903 Members | 2,137 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,903 developers. It's quick & easy.

how to convert varchar to numeric ?

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
4 59116
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
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
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
"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.

Similar topics

reply views Thread by Bill | last post: by
4 posts views Thread by Dean G | last post: by
1 post views Thread by Robert Fitzpatrick | last post: by
5 posts views Thread by 2redline | last post: by
1 post views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.