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.
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.
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.
Reply With Quote
Bookmarks