How to convert from CHAR to Numeric in the table

Thread View: comp.databases.ms-sqlserver

3 messages

[T] Back to Threads [V] Tree View [G] Group Articles

3 total messages Started by Mac Thu, 29 Sep 2005 14:29

#77

Author: Mac
Date: Thu, 29 Sep 2005 14:29

11 lines
1436 bytes

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

Re: How to convert from CHAR to Numeric in the table

#78

Author: Simon Hayes
Date: Thu, 29 Sep 2005 14:34

10 lines
1753 bytes

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

Re: How to convert from CHAR to Numeric in the table

#79

Author: Tibor Karaszi
Date: Thu, 29 Sep 2005 14:34

26 lines
1923 bytes

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 above 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 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
>

Thread Navigation

This is a paginated view of messages in the thread with full content displayed inline.

Messages are displayed in chronological order, with the original post highlighted in green.

Use pagination controls to navigate through all messages in large threads.

Back to All Threads