这是我的测试过程..通过了.. 看看你能不能用上.. 表 create table test(str varchar2(20),id number); 数据 insert into test values('你好ABC123!',1); insert into test values('hello北京!789',2); 存储过程 create or replace procedure replaceString is strTmp test.str%type; strTmp1 test.str%type; intId number; cursor curStr is select str, id from test; begin open curStr; loop fetch curStr into strTmp, intId; exit when curStr%notfound; strTmp1 := ''; for i in 1 .. length(strTmp) loop if ascii(substr(strTmp, i, 1)) = 33 then strTmp1 := strTmp1 || '!'; elsif length(substr(strTmp, i, 1)) != lengthb(substr(strTmp, i, 1)) then strTmp1 := strTmp1 || '?'; elsif ascii(substr(strTmp, i, 1)) >= 65 and ascii(substr(strTmp, i, 1)) <= 122 then strTmp1 := strTmp1 || substr(strTmp, i, 1); elsif ascii(substr(strTmp, i, 1)) >= 48 and ascii(substr(strTmp, i, 1)) <= 57 then strTmp1 := strTmp1 || to_char(9 - to_number(substr(strTmp, i, 1))); end if; end loop; update test set str = strTmp1 where id = intId; commit; end loop; close curStr; end; 结果 1 ??ABC876! 2 hello??!210
简单的方法:SELECT TRANSLATE(REGEXP_REPLACE('s中国ABC123!', '[^0-9a-zA-Z!]', '?'), '1234567890!', '8765432109!') "REGEXP_REPLACE" FROM DUAL;可以根据你的变换对照关系修改上面的字符
SELECT TRANSLATE(CONVERT('s中国ABC123!','US7ASCII'), '1234567890!', '8765432109!') FROM dual
看看你能不能用上..
表
create table test(str varchar2(20),id number);
数据
insert into test values('你好ABC123!',1);
insert into test values('hello北京!789',2);
存储过程
create or replace procedure replaceString is
strTmp test.str%type;
strTmp1 test.str%type;
intId number;
cursor curStr is
select str, id from test;
begin
open curStr;
loop
fetch curStr
into strTmp, intId;
exit when curStr%notfound;
strTmp1 := '';
for i in 1 .. length(strTmp) loop
if ascii(substr(strTmp, i, 1)) = 33 then
strTmp1 := strTmp1 || '!';
elsif length(substr(strTmp, i, 1)) != lengthb(substr(strTmp, i, 1)) then
strTmp1 := strTmp1 || '?';
elsif ascii(substr(strTmp, i, 1)) >= 65 and
ascii(substr(strTmp, i, 1)) <= 122 then
strTmp1 := strTmp1 || substr(strTmp, i, 1);
elsif ascii(substr(strTmp, i, 1)) >= 48 and
ascii(substr(strTmp, i, 1)) <= 57 then
strTmp1 := strTmp1 || to_char(9 - to_number(substr(strTmp, i, 1)));
end if;
end loop;
update test set str = strTmp1 where id = intId;
commit;
end loop;
close curStr;
end;
结果
1 ??ABC876!
2 hello??!210
简单的方法:SELECT TRANSLATE(REGEXP_REPLACE('s中国ABC123!', '[^0-9a-zA-Z!]', '?'),
'1234567890!',
'8765432109!') "REGEXP_REPLACE"
FROM DUAL;可以根据你的变换对照关系修改上面的字符
'1234567890!', '8765432109!')
FROM dual