Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

how can i change a varchar2 column to a number column

Posted on 2004-10-25
18
Medium Priority
?
1,966 Views
Last Modified: 2012-08-14
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
Comment
Question by:vihar123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 4
  • +2
18 Comments
 
LVL 15

Assisted Solution

by:andrewst
andrewst earned 1000 total points
ID: 12399326
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
 
LVL 6

Expert Comment

by:morphman
ID: 12399421
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
 
LVL 9

Expert Comment

by:konektor
ID: 12399446
drop constraint before renaming and create finishing ...
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:vihar123
ID: 12399447
>>>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
 
LVL 15

Expert Comment

by:andrewst
ID: 12399451
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
 
LVL 15

Expert Comment

by:andrewst
ID: 12399474
> 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
 
LVL 6

Expert Comment

by:morphman
ID: 12399476
cool, learn something new every day :-)

cheers
0
 

Author Comment

by:vihar123
ID: 12399794
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
 
LVL 15

Expert Comment

by:andrewst
ID: 12399828
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
 

Author Comment

by:vihar123
ID: 12399878
>>>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
 
LVL 15

Expert Comment

by:andrewst
ID: 12399988
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
 

Author Comment

by:vihar123
ID: 12400101
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
 
LVL 6

Expert Comment

by:morphman
ID: 12400161
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
 

Author Comment

by:vihar123
ID: 12400295
select is_number('0015.000 ') from dual;

for me its giving 0
0
 
LVL 15

Expert Comment

by:andrewst
ID: 12400299
Maybe you have the NLS setting for decimal separator set to "," instead of "."?
0
 
LVL 6

Accepted Solution

by:
morphman earned 1000 total points
ID: 12400321
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 12400497
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
 

Author Comment

by:vihar123
ID: 12401332
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to recover a database from a user managed backup

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question