create or replace function func_chinese
( p_str in varchar2, -- 输入的字符串 p_code in varchar2, -- dump(字符串) p_chinese in pls_integer -- 1, 提取汉字, 非1, 提取非汉字) return varchar2as v_code varchar2(32767) := substr(p_code,instr(p_code,':')+2); v_chinese varchar2(32767) := ''; v_non_chinese varchar2(32767) := ''; v_comma pls_integer; v_code_h pls_integer; v_code_l pls_integer;begin if p_str is not null then for i in 1..length(p_str) loop if lengthb(substr(p_str,i,1))=2 then v_comma := instr(v_code,','); v_code_h := to_number(substr(v_code,1,v_comma-1)); v_code_l := to_number(substr(v_code,v_comma+1,abs(instr(v_code,',',1,2)-v_comma-1))); if (v_code_h>=176 and v_code_h<=247 and v_code_l>=161 and v_code_l<=254) or (v_code_h>=129 and v_code_h<=160 and v_code_l>=64 and v_code_l<=254 and nvl(v_code_l,127)!=127) or (v_code_h>=170 and v_code_h<=254 and v_code_l>=64 and v_code_l<=160 and nvl(v_code_l,127)!=127) then v_chinese := v_chinese||substr(p_str,i,1); else v_non_chinese := v_non_chinese||substr(p_str,i,1); end if; v_code := ltrim(v_code,'1234567890'); v_code := ltrim(v_code,','); else v_non_chinese := v_non_chinese||substr(p_str,i,1); end if; v_code := ltrim(v_code,'1234567890'); v_code := ltrim(v_code,','); end loop; if p_chinese = 1 then return v_chinese; else return v_non_chinese; end if; else return ''; end if;end;/SQL> select * from t;NAME----------------------新年快乐X狿X狿199春天会来的-----------------------实验1: 提取汉字-----------------------select name,func_chinese(name,dump(name),1) result from t;NAME RESULT------------------------------ -----------新年快乐X 新年快乐狿X 狿狿 狿199春天会来的 春天会来的-----------------------实验2: 提取非汉字-----------------------select name,func_chinese(name,dump(name),0) result from t;NAME RESULT------------------------------ -------新年快乐X X狿X X狿199 199春天会来的-----------------------