|
Home > Archive > MS SQL Server > September 2005 > How to convert from CHAR to Numeric in the table
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
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
| |
| Simon Hayes 2005-09-29, 11:23 am |
| 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
| |
| Tibor Karaszi 2005-09-29, 11:23 am |
| 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
>
| |
|
| 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 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
>
>
>
|
|
|
|
|