Re: Cast char to number

On 24/02/2010 19:53, Christine Penner wrote:
> I don't understand what you mean. This is a column in a table that is
> already a char and has numbers in it. I want it to be a number field not
> character. How can I change the data type of that column without loosing
> the data I have in it?
>
> Christine
>
> At 11:38 AM 24/02/2010, you wrote:
>> In response to Christine Penner <christine(at)ingenioussoftware(dot)com>:
>>
>> > I have a character field I want to change to a number. The values in
>> > that field are all numbers that may or may not be padded with spaces
>> > or 0's. What is the best way to do that?
>>
>> Put the values in numeric fields to begin with and cast to chars as
>> needed. Basically reverse what you're doing.

I think what he means is that you should have been doing the reverse to
begin with - storing numbers in the database as numeric columns, and
then casting them to a character format as needed for display.

However, to address your immediate problem, you could try something like
this:

(i) Create a new column of type numeric or integer as appropriate.
(ii) update your_table set new_column = CAST(trim(both ' 0' from
old_column) as numeric)
(iii) Drop the old column, as well as any constraints depending on it.
(iv) Rename the new column to the same name as the old column
(v) Recreate any of the constraints dropped in step (iii).

I think the cast in step (ii) might not be necessary - not sure about this.

HTH.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod(at)iol(dot)ie