Copying of data from varchar2 to number in a same

Question

I have two columns and i need to copy of data from column VISITSAUTHORIZED to NEWVISITS, When i use below command to copy data i am getting an error message "invalid number".Can anyone correct this ?

 VISITSAUTHORIZED  VARCHAR2(10)
 NEWVISITS         NUMBER(8)
 SQL> update patientinsurance set NEWVISITS=VISITSAUTHORIZED ;
 ERROR at line 1:
 ORA-01722: invalid number

Answer 1

It depends what kind of data you have in your old column. If it is all consistently formatted then you might be able to do:

update patientinsurance
set newvisits = to_number(visitsauthorized, '<format model>')

But it sounds more likely that you have something less easy to deal with. (The joys of storing data as the wrong datatype, which I assume is what you're now correcting). If there are rogue characters then you could use translate to get rid of them, perhaps, but you'd have to wonder about the integrity of the data and the values you end up with.

You can do something like this to display all the values that can't be converted, which may give you an idea of the best way to proceed - if there are only a few you might be able to correct them manually before re-running your update:

set serveroutput on
declare
  newvisits number;
  number_format_exception exception;
  pragma exception_init(number_format_exception, -6502);
begin
  for r in (select id, visitsauthorized from patientinsurance) loop
    begin
      newvisits := to_number(r.visitsauthorized);
    exception
      when number_format_exception then
        dbms_output.put_line(sqlcode || ' ID ' || r.id
          || ' value ' || r.visitsauthorized);
    end;
  end loop;
end;
/

This is guessing you have a unique identifier field called ID, but change that as appropriate for your table, obviously.

Another approach is to convert the numbers that are valid and skip over the rest, which you can do with an error logging table:

exec dbms_errlog.create_error_log(dml_table_name => 'PATIENTINSURANCE');

merge into patientinsurance target
using (select id, visitsauthorized from patientinsurance) source
on (target.id = source.id)
when matched then
  update set target.newvisits = source.visitsauthorized
log errors into err$_patientinsurance reject limit unlimited;

You can then query the error table to see what failed:

select id, visitsauthorized, ora_err_number$
from err$_patientinsurance;

Or see which records in your main table have newvisits still null. Analysing your data should probably be the first step though.

---

If you want to strip out all non-numeric characters and treat whatever is left as a number then you can change the merge to do:

...
update set target.newvisits = regexp_replace(source.visitsauthorized,
  '[^[:digit:]]', null)

But then you probably don't need the merge, you can just do:

update patientinsurance set newvisits = regexp_replace(visitsauthorized,
  '[^[:digit:]]', null);

This will strip out any group or decimal separators as well, which might not be an issue, particularly as you're inserting into a number(8) column. But you could preserve those if you wanted to, by changing the pattern to '[^[:digit:].,]'... though that could give you other problems still, potentially.

You can also do this with translate if the regex is too slow.