--提取汉字,去掉非汉子数据
--创建函数
/*使用ASCII码来区分中文和其它字符,中文的ASCII码值的范围是45217~63486,
根据这个来实现,我们就需要使用ORACLE中的函数ASCII,用它来返回ASCII值。*/
create or replace function FUNC_CHINESE(custName varchar2) return varchar2 is
Result varchar2(100);
tmp_custName varchar2(100);
count_str number;
i number:=1;
str_ascii number;
current_char varchar2(10);
begin
select length(custName) into count_str from dual;
while i<count_str loop
current_char:=substr(custName,i,1);
select ASCII(current_char) into str_ascii from dual;
if str_ascii>45216 then
tmp_custName:=tmp_custName||current_char;
end if;
i:=i+1;
end loop;
Result:=tmp_custName;
return(Result);
end FUNC_CHINESE;
--创建测试数据
create table tests(
txt varchar2(200)
);
insert into tests (txt)
values('你a好,你1吃D饭S了s');
--测试函数
select FUNC_CHINESE(txt) from tests