create or replace function func_chinese
(
p_str in varchar2, -- 输入的字符串
p_code in varchar2, -- dump(字符串)
p_chinese in pls_integer -- 1, 提取汉字, 非1, 提取非汉字
) return varchar2
as
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
春天会来的
-----------------------