SQL语句 保留中文字符以及去掉中文字符

-- 保留或删除字符串中中文字符SQL函数


create or replace function f_deal_off_chinese(x varchar)
--去除中文字符，保留非中文字符
 return varchar as
  y varchar(4000);
begin
  for i in 1 .. length(x) loop
    if ascii(substr(x, i, 1)) <= 127 then
      y := y || substr(x, i, 1);
    end if;
  end loop;
  return y;
end f_deal_off_chinese;


create or replace function f_deal_keep_chinese(x varchar)
--保留中文字符，去除非中文字符
 return varchar as
  y varchar(4000);
begin
  for i in 1 .. length(x) loop
    if ascii(substr(x, i, 1)) > 127 then
      y := y || substr(x, i, 1);
    end if;
  end loop;
  return y;
end f_deal_keep_chinese;


--测试
with tmpTable as
 (select '我abc123' a from dual union all select 'aaa中国123' a from dual)
select f_deal_keep_chinese(a) as keep_chi,
       f_deal_off_chinese(a) as off_chi,
       a
  from tmpTable;