How to convert from CHAR to Numeric in the table

I have imported some data to sql2k from my old system.  Somehow, it imported
invoice amount to char type.

I just created another column called invamt2 type NUMERIC so I can copy or
convert content of invamt which is type CHAR.  There are about 50,000
records.

How can I convert/cast from char type to numeric type ?

Thanks

If the only problem is the column data type, and you know the data
itself is already numeric, then you can just alter the column data type
directly:

alter table SomeTable alter column invamt numeric(10,5)

See "CAST and CONVERT" in Books Online for general information about
data type conversion.

Simon

If you don't have any dirty data, you can do:

UPDATE tblname
SET invamt2 = CAST(invamt AS numeric(..., ...))

If you do have dirty data, you will get a datatype conversion error from abo
ve and need to clean
your data first.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/


"Mac" <mac@hotmail.com> wrote in message news:TSS_e.10727$WT3.7999@trnddc03...
>
> I have imported some data to sql2k from my old system.  Somehow, it import
ed invoice amount to
> char type.
>
> I just created another column called invamt2 type NUMERIC so I can copy or
 convert content of
> invamt which is type CHAR.  There are about 50,000 records.
>
> How can I convert/cast from char type to numeric type ?
>
> Thanks
>

Play around with this

declare @chrValue varchar(50)
select @chrValue ='123.5678'

select  convert(numeric(7,4)
,@chrValue)
GO
declare @chrValue varchar(50)
select @chrValue ='12388888.5678'

select  convert(numeric(12,4
),@chrValue)
GO

http://sqlservercode.blogspot.com/




"Mac" wrote:

>
> I have imported some data to sql2k from my old system.  Somehow, it import
ed
> invoice amount to char type.
>
> I just created another column called invamt2 type NUMERIC so I can copy or
> convert content of invamt which is type CHAR.  There are about 50,000
> records.
>
> How can I convert/cast from char type to numeric type ?
>
> Thanks
>
>
>