范围之内 [PHP] create or replace function get_chinese(p_name in varchar2) return varchar2 as v_code varchar2(30000) := ''; v_chinese varchar2(4000) := ''; v_comma pls_integer; v_code_q pls_integer; v_code_w pls_integer; begin if p_name is not null then select replace(substrb(dump(p_name,1010),instrb(dump(p_name,1010),'ZHS16GBK:')),'ZHS16GBK: ','') into v_code from dual where rownum=1; for i in 1..length(p_name) loop if lengthb(substr(p_name,i,1))=2 then v_comma := instrb(v_code,','); v_code_q := to_number(substrb(v_code,1,v_comma-1)); v_code_w := to_number(substrb(v_code,v_comma+1,abs(instrb(v_code,',',1,2)-v_comma-1))); if v_code_q>=176 and v_code_q<=247 and v_code_w>=161 and v_code_w<=254 then v_chinese := v_chinese||substr(p_name,i,1); end if; v_code := ltrim(v_code,'1234567890'); v_code := ltrim(v_code,','); end if; v_code := ltrim(v_code,'1234567890'); v_code := ltrim(v_code,','); end loop; return v_chinese; else return ''; end if; end; / . [/PHP]
好,现在来执行一些语句 SQL> select * from test;
NAME -------------------- ,啊OO10哈 你好aa 大家好aa/ ☆大海123 ★ABC
5 rows selected.
1. 列出有汉字的记录 SQL> select name from test where length(get_chinese(name))>0;
NAME -------------------- ,啊OO10哈 你好aa 大家好aa/ ☆大海123
4 rows selected.
2. 列出有汉字的记录,并且只列出汉字
SQL> select get_chinese(name) from test where length(get_chinese(name))>0;
[PHP] create or replace function get_chinese ( p_name in varchar2, p_chinese in varchar2 ) return varchar2 as v_code varchar2(30000) := ''; v_chinese varchar2(4000) := ''; v_non_chinese varchar2(4000) := ''; v_comma pls_integer; v_code_q pls_integer; v_code_w pls_integer; begin if p_name is not null then select replace(substrb(dump(p_name,1010),instrb(dump(p_name,1010),'ZHS16GBK:')),'ZHS16GBK: ','') into v_code from dual where rownum=1; for i in 1..length(p_name) loop if lengthb(substr(p_name,i,1))=2 then v_comma := instrb(v_code,','); v_code_q := to_number(substrb(v_code,1,v_comma-1)); v_code_w := to_number(substrb(v_code,v_comma+1,abs(instrb(v_code,',',1,2)-v_comma-1))); if v_code_q>=176 and v_code_q<=247 and v_code_w>=161 and v_code_w<=254 then v_chinese := v_chinese||substr(p_name,i,1); else v_non_chinese := v_non_chinese||substr(p_name,i,1); end if; v_code := ltrim(v_code,'1234567890'); v_code := ltrim(v_code,','); else v_non_chinese := v_non_chinese||substr(p_name,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; /