how can i change a varchar2 column to a number column

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Even if there is data in the tables, it may be that converting to a number field is OK, as the varchar2 fields may only contain numbers.

If the varchar2 field contains any chars, you will need to to what andrewst suggested.

konektor

drop constraint before renaming and create finishing ...

ASKER

>>>alter table offrdet1 modify ang_nr number(10,3);

i deleted the rows and tried the above one, its giving me this error

Ora-02267:column type incompatible with referenced column type

No, the table must be empty:

SQL> create table t2 (x varchar2(12) not null);

Table created.

SQL> insert into t2 values ('123');

1 row created.

SQL> alter table t2 modify x number;
alter table t2 modify x number
*
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype

> Ora-02267:column type incompatible with referenced column type
You need to drop the foreign key constraint first, then change the column type in both tables, then re-create the constraint.

cool, learn something new every day :-)

cheers

ASKER

insert /*+ append */ into offreg2 (ang_nr, proj_nr, prod_bez, kunde_nr, faktor, basisprojekt)
select to_number(ang_nr), proj_nr, prod_bez, kunde_nr, faktor, basisprojekt from anska.offreg;

tried this its giving me
ORA-01722: invalid number

That means that some of your ang_nr values are not in fact numbers! You need to clean up your data before you can convert it.

You can create a simple "is_number" function to find the rogue data:

create or replace function is_number (x in varchar2) return integer is
v_num number;
begin
v_num := x;
return 1;
exception
when others then return 0;
end;
/

Then:

select ang_nr, ... from ... where is_number(ang_nr) = 0;

ASKER

>>>That means that some of your ang_nr values are not in fact numbers! You need to clean up your data before you can convert it.

ya its not just number, as i said earlier its something like four number then fullstop and then three numbers,

for eg;. 1425.000

cleaning up the data can solve the problem but i dont have rights.....

1425.000 IS a number! But you obvioyusly have something else, like '1425XYZ' or 'HELLO WORLD' in that column also!

If you can't clean the data then you can't convert it - end of story!

ASKER

create or replace function is_number (x in varchar2) return integer is
v_num number;
begin
v_num := x;
return 1;
exception
when others then return 0;
end;
/

select ang_nr from offreg1 where is_number(ang_nr) = 0;

when im doing this its giving me these results, and i dont find any characters in them except fullstop

ANG_NR
0015.000
1172.001
1126.001
1325.001
1219.001
1200.001
1425.001
0017.000

Hmm, works for me

control@assist_o> select is_number('0015.000 ') from dual;

IS_NUMBER('0015.000')
---------------------
1
even with a trailing space as you have pasted above.

ASKER

select is_number('0015.000 ') from dual;

for me its giving 0

Maybe you have the NLS setting for decimal separator set to "," instead of "."?

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

You didn't post what version of oracle you are on.

Assuming you are on a high enough version: I just wanted to add an alternative to creating a whole new table:
Add a new column to the table that is numeric. Update the new column with the appropriate data (following all the the cleansing already mentioned), drop the varchar column, rename the numeric column to the old name.

drop table tab1;
create table tab1( col1 varchar2(100) );

insert into tab1 values('123');
commit;

alter table tab1 add tempCol number(10,2);
update tab1 set tempCol=to_number(col1);

alter table tab1 drop column col1;

alter table tab1 rename column tempCol to col1;