只能用函数啦:create or replace function replace_str(i_str varchar2, i_replace varchar2) return varchar2 is o_str varchar2(1000); begin o_str := ''; for i in 1 .. length(i_str) loop o_str := o_str||i_replace||substr(i_str,i,1); end loop; return o_str; end; /select replace_str('1111','0') from dual;
update a set col1=replace_str(col2,'0');
SQL> edi 已写入 file afiedt.buf 1 create or replace procedure p1(v1 varchar2,v2 out varchar2) 2 as 3 num number; 4 begin 5 num:=1; 6 v2:=''; 7 while num <=length(v1) loop 8 v2:=v2||'0'||substr(v1,num,1); 9 num:=num+1; 10 end loop; 11* end; SQL> /过程已创建。SQL> declare 2 zf varchar2(100); 3 zf1 varchar2(1000); 4 begin 5 zf:='1111'; 6 p1(zf,zf1); 7 dbms_output.put_line(zf1); 8 end; 9 / 01010101 PL/SQL 过程已成功完成。
SQL> edi 已写入 file afiedt.buf 1 create or replace function func1 (v1 varchar2) return varchar2 2 as 3 num number; 4 v2 varchar2(1000); 5 begin 6 num:=1; 7 v2:=''; 8 while num <=length(v1) loop 9 v2:=v2||'0'||substr(v1,num,1); 10 num:=num+1; 11 end loop; 12 return v2; 13* end; SQL> /函数已创建。SQL> create table tb(col1 varchar2(10),col2 varchar2(10));表已创建。SQL> insert into tb(col2) values('11111');已创建 1 行。SQL> update tb set col1=func1(col2);已更新 1 行。SQL> select * from tb;COL1 COL2 ---------- ---------- 0101010101 11111
-- 考虑 原字符串为空的情况,完善一下: create or replace function replace_str(i_str varchar2, i_replace varchar2) return varchar2 is o_str varchar2(1000); begin o_str := ''; if i_str is not null then for i in 1 .. length(i_str) loop o_str := o_str||i_replace||substr(i_str,i,1); end loop; end if; return o_str; end; /
return varchar2
is
o_str varchar2(1000);
begin
o_str := '';
for i in 1 .. length(i_str) loop
o_str := o_str||i_replace||substr(i_str,i,1);
end loop;
return o_str;
end;
/select replace_str('1111','0') from dual;
SQL> edi
已写入 file afiedt.buf 1 create or replace procedure p1(v1 varchar2,v2 out varchar2)
2 as
3 num number;
4 begin
5 num:=1;
6 v2:='';
7 while num <=length(v1) loop
8 v2:=v2||'0'||substr(v1,num,1);
9 num:=num+1;
10 end loop;
11* end;
SQL> /过程已创建。SQL> declare
2 zf varchar2(100);
3 zf1 varchar2(1000);
4 begin
5 zf:='1111';
6 p1(zf,zf1);
7 dbms_output.put_line(zf1);
8 end;
9 /
01010101
PL/SQL 过程已成功完成。
SQL> edi
已写入 file afiedt.buf 1 create or replace function func1 (v1 varchar2) return varchar2
2 as
3 num number;
4 v2 varchar2(1000);
5 begin
6 num:=1;
7 v2:='';
8 while num <=length(v1) loop
9 v2:=v2||'0'||substr(v1,num,1);
10 num:=num+1;
11 end loop;
12 return v2;
13* end;
SQL> /函数已创建。SQL> create table tb(col1 varchar2(10),col2 varchar2(10));表已创建。SQL> insert into tb(col2) values('11111');已创建 1 行。SQL> update tb set col1=func1(col2);已更新 1 行。SQL> select * from tb;COL1 COL2
---------- ----------
0101010101 11111
create or replace function replace_str(i_str varchar2, i_replace varchar2)
return varchar2
is
o_str varchar2(1000);
begin
o_str := '';
if i_str is not null then
for i in 1 .. length(i_str) loop
o_str := o_str||i_replace||substr(i_str,i,1);
end loop;
end if;
return o_str;
end;
/