写一个函数吧。
16:28:45 SQL> create or replace function get_char(v_char varchar2)
16:29:09 2 return varchar2 as
16:29:09 3 v_return varchar2(100);
16:29:09 4 v_temp varchar2(5);
16:29:09 5 begin
16:29:09 6 for i in 1..length(v_char) loop
16:29:09 7 v_temp:=substr(v_char,i,1);
16:29:10 8 if upper(v_temp)>='A' and upper(v_temp)<='Z' then
16:29:26 9 v_return:=v_return||v_temp;
16:29:30 10 end if;
16:29:30 11 end loop;
16:29:30 12 return v_return;
16:29:30 13 end get_char;
16:29:30 14 /函数已创建。已用时间: 00: 00: 00.46
16:29:30 SQL> select get_char('awefw142r_$awwef') from dual;GET_CHAR('AWEFW142R_$AWWEF')
-------------------------------------------------------------------------
awefwrawwef已用时间: 00: 00: 00.16
16:29:33 SQL>
16:28:45 SQL> create or replace function get_char(v_char varchar2)
16:29:09 2 return varchar2 as
16:29:09 3 v_return varchar2(100);
16:29:09 4 v_temp varchar2(5);
16:29:09 5 begin
16:29:09 6 for i in 1..length(v_char) loop
16:29:09 7 v_temp:=substr(v_char,i,1);
16:29:10 8 if upper(v_temp)>='A' and upper(v_temp)<='Z' then
16:29:26 9 v_return:=v_return||v_temp;
16:29:30 10 end if;
16:29:30 11 end loop;
16:29:30 12 return v_return;
16:29:30 13 end get_char;
16:29:30 14 /函数已创建。已用时间: 00: 00: 00.46
16:29:30 SQL> select get_char('awefw142r_$awwef') from dual;GET_CHAR('AWEFW142R_$AWWEF')
-------------------------------------------------------------------------
awefwrawwef已用时间: 00: 00: 00.16
16:29:33 SQL>
ta varchar2(100);
begin
ta:=replace(translate(&tb,'需要过滤的字符',' '),' ','');
dbms_output.put_line(ta);
end;
/for example:过滤数字和下划线
16:38:26 SQL> declare
16:38:36 2 ta varchar2(100);
16:38:36 3 begin
16:38:36 4 ta:=replace(translate(&tb,'0123456789_',' '),' ','');
16:38:36 5 dbms_output.put_line(ta);
16:38:36 6 end;
16:38:36 7 /
输入 tb 的值: 'dfef_df123d78649d'
原值 4: ta:=replace(translate(&tb,'0123456789_',' '),' ','');
新值 4: ta:=replace(translate('dfef_df123d78649d','0123456789_',' '),' ',''
);
dfefdfddPL/SQL 过程已成功完成。已用时间: 00: 00: 00.15