将数据从varchar2复制到同一表中的数字 [英] Copying of data from varchar2 to number in a same table

问题描述

我有两列，我需要将数据从VISITSAUTHORIZED列复制到NEWVISITS，当我使用以下命令复制数据时，我收到一条错误消息无效编号".有人可以纠正吗?

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

推荐答案

这取决于您在旧列中拥有的数据类型.如果所有格式都一致，则您可以执行以下操作:

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>')

但是听起来您不太容易处理. (将数据存储为错误的数据类型的乐趣，我认为这就是您现在要纠正的错误).如果存在流氓字符，则可以使用translate除去它们，但是您不得不怀疑数据的完整性以及最终得到的值.

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;
/

这是在猜测您有一个名为ID的唯一标识符字段，但是显然要根据您的表进行更改.

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;

或查看主表中哪些记录仍为newvisits.不过，分析数据可能应该是第一步.

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);

这也将去除任何组分隔符或十进制分隔符，这可能不是问题，尤其是当您插入number(8)列时.但是，如果愿意，可以通过将模式更改为'[^[:digit:].,]' ...来保留这些内容，尽管这可能还会给您带来其他问题.

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.

如果正则表达式太慢，您也可以使用translate执行此操作.

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

这篇关于将数据从varchar2复制到同一表中的数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！