Re: data type conversion

First you create number column in your table

If you try update statement to convert numbers like this:

update mytable
set numcolumn=to_number(varchar2column)

it will fail if it hits something that can not be converted to number. Also, there is no function that will tell you if something can be converted to number. So, there are two solutions:

Write your own function. Something like this

create functin check_number(inVar varchar2) return number is
v_temp number;
begin
v_temp=to_number(inVar);
return 1;
exception
when others then
return 0;
end;

and then do

update mytable
set numcolumn=to_number(varchar2column)
where check:number(varchar2column)=1;

if this doesn't help, you might do that in procedure:

procedure convert_to_number is

cursor c is select * from mytable for update; begin

for cc in cc loop

    begin

        update mytable set numcolumn=to_number(varchar2column)
        where current of c;

        exception
        when others then null;

commit;
end;

when something can't be converted to number, exception will be raised and handeled inside cursor.

After this, drop the varchar2column, and create column with the same name, but make it number column. (You don't want to change your applications because you changed the name of the column)

Then fill the new column

update mytable
set varchar2column=numbercolumn;

of course, this new varchar2column is now created as number;

by the way, check syntax, I'm sure that there I made some mistakes.

Vissu <vissuyk_at_yahoo.com> wrote in message news:2bedd6a7.0209091830.47544562_at_posting.google.com...
> Hi All,
>
> We have couple of columns in a table that were created as VARCHAR2.
> These columns are to hold numbers. Every once in a while our database
> receives junk data from users and the scripts that does computations
> are failing since they require numbers.
>
> I want clean up these tables. I want to add a new column and drop the
> old column. The question is what is best way to move the data from old
> column to the new. I only want to copy values that are numbers. How
> should I do this ?
> Is it just a update statement ?.
>
> Any help is appreciated.
>
> Thanks a lot
>
> Vissu
Received on Sun Sep 15 2002 - 18:26:13 CEST