[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2146
  • Last Modified:

how can i change a varchar2 column to a number column

im having two tables for eg;.
1.
------------
CREATE TABLE offrdet1 (
  ang_nr    VARCHAR2(12) NOT NULL,
  ver_nr    NUMBER(2,0)  NOT NULL,
  ver_besh  VARCHAR2(40) NOT NULL,
  gesc_jahr NUMBER(4,0)  NOT NULL,
  curr      VARCHAR2(3)  NOT NULL,
  ges_kalk  VARCHAR2(20) NULL,
  kalk_mek  VARCHAR2(20) NULL,
  kalk_ele  VARCHAR2(20) NULL,
  realized  VARCHAR2(1)  NULL,
  ref_nr    VARCHAR2(12) NULL
)
  PCTUSED    0
/

ALTER TABLE offrdet1
  ADD PRIMARY KEY (
    ang_nr,
    ver_nr
  )
/

ALTER TABLE offrdet1
  ADD CONSTRAINT offer FOREIGN KEY (
    ang_nr
  ) REFERENCES offreg1 (
    ang_nr
  )
/


2.
----
CREATE TABLE offreg1 (
  ang_nr       VARCHAR2(12) NOT NULL,
  proj_nr      VARCHAR2(11) NULL,
  prod_bez     VARCHAR2(40) NOT NULL,
  kunde_nr     NUMBER(5,0)  NOT NULL,
  faktor       NUMBER(4,2)  NOT NULL,
  basisprojekt NUMBER(4,0)  NULL
)
  PCTUSED    0
/

ALTER TABLE offreg1
  ADD PRIMARY KEY (
    ang_nr
  )
/
----

now i want to change the ang_nr column in both the tables into number? im quite new to db, so please help me with all your valuable comments....ang_nr values are something like this (1425.001, 1552.254) etc....

thanks

0
vihar123
Asked:
vihar123
  • 6
  • 6
  • 4
  • +2
2 Solutions
 
andrewstCommented:
If there is no data in the tables then you can simply:

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

Otherwise, you will need to create a new table with the required column data types and copy the data:

insert /*+ append */ into new_offrdet1 (ang_nr, ... )
select to_number(ang_nr), ...
from offrdet1;

rename offrdet1 to old_offrdet1;

rename new_offrdet1 to offrdet1;

(Then reinstate all the indexes, constraints etc.)
0
 
morphmanCommented:
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.
0
 
konektorCommented:
drop constraint before renaming and create finishing ...
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
vihar123Author Commented:
>>>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
0
 
andrewstCommented:
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
0
 
andrewstCommented:
> 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.
0
 
morphmanCommented:
cool, learn something new every day :-)

cheers
0
 
vihar123Author Commented:
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
0
 
andrewstCommented:
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;
0
 
vihar123Author Commented:
>>>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.....
0
 
andrewstCommented:
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!
0
 
vihar123Author Commented:
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
0
 
morphmanCommented:
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.
0
 
vihar123Author Commented:
select is_number('0015.000 ') from dual;

for me its giving 0
0
 
andrewstCommented:
Maybe you have the NLS setting for decimal separator set to "," instead of "."?
0
 
morphmanCommented:
ok, try

insert /*+ append */ into offreg2 (ang_nr, proj_nr, prod_bez, kunde_nr, faktor, basisprojekt)
select to_number(ang_nr,'9999999999.000'), proj_nr, prod_bez, kunde_nr, faktor, basisprojekt from anska.offreg;
0
 
slightwv (䄆 Netminder) Commented:
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;

0
 
vihar123Author Commented:
actually im calling this data into a tree node in my java application and parsing it to a string .....so whenever im trying to access the node ang_nr its throwing this exception, i thought of changing the column to number and see, even after that im unable to solve it, im still getting the same error, i also tried with the nls, but still getting the same error, gone out of ideas, shld try something new....maybe parsing....
anyway thanks for all the help.

0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 6
  • 6
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now