Solved

how can i change a varchar2 column to a number column

Posted on 2004-10-25
1,652 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
Question by:vihar123
    18 Comments
     
    LVL 15

    Assisted Solution

    by:andrewst
    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
    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
    drop constraint before renaming and create finishing ...
    0
     

    Author Comment

    by:vihar123
    >>>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
    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
    > 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
    cool, learn something new every day :-)

    cheers
    0
     

    Author Comment

    by:vihar123
    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
    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
    >>>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
    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
    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
    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
    select is_number('0015.000 ') from dual;

    for me its giving 0
    0
     
    LVL 15

    Expert Comment

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

    Accepted Solution

    by:
    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 75

    Expert Comment

    by: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;

    0
     

    Author Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
    Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

    856 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

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now