DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: migration of character to number

  1. #1
    Join Date
    Mar 2001
    Posts
    13
    migrating data from a table with a varchar2 data type to a table with a column of a number data type. Any good ideas on how to do this? I know I an use the replace function (but only allows one value to be passed in at a time) and also the decode statement (gets a little hairy because of the else part, I don't have an else value to use). These don't seem to be the best options. Any ideas appreciated.

  2. #2
    Join Date
    Aug 2001
    Posts
    75
    If the data in the varchar2 column is number then oracle will automatically convert it to number.

    e.g.
    create table test ( x number);

    insert into test values ('12');

    Since oracle can convert string 12 to number 12, this should
    not be problem.
    However, if oracle can't convert, then it will return error
    ORA-01722: invalid number

    You can easily write a plsql block and catch this exception.

    Sanjay
    OCP 8i

  3. #3
    Join Date
    Mar 2001
    Posts
    13
    I was able to find a consistency in the values and used the instr function along with with the substr function to strip out all of the non-numeric characters. Thanks for the reply.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width