文件名称：Oracle 汉字转全拼 拼音首字母
文件大小：38KB
文件格式：SQL
更新时间：2014-05-13 19:22:16
Oracle 汉字转全拼 拼音首字母
--此函数默认返回汉字拼音的首字母，第二个参数不为空则返回全拼。 create or replace function f_getFirstOrFullSpell(p_cnStr In varchar2,p_sign In number default null) return varchar2 as lv_spell varchar2(200); lv_temp Varchar2(10); lv_char varchar2(10); --lv_bytes varchar2(100); li_bytes Integer; --li_pos Integer; begin if p_cnStr is null then return ''; end if; for i In 1..length(p_cnStr) loop lv_char:=substr(p_cnStr,i,1); if lengthb(lv_char) = 1 then lv_spell:=lv_spell||lv_char; elsif lengthb(lv_char) = 2 then --Select replace(substrb(dump(lv_char,1010),instrb(dump(lv_char,1010),'ZHS16GBK:')),'ZHS16GBK: ','') Into lv_bytes from dual; --li_pos:=instr(lv_bytes,','); --li_bytes:=substr(lv_bytes,1,li_pos-1)*256+substr(lv_bytes,li_pos+1)-256*256; Select ascii(lv_char)-256*256 Into li_bytes From dual; select max(spell) Into lv_temp from table(f_getSpellcode) where code<=li_bytes; if p_sign is null then lv_spell:=lv_spell||substr(lv_temp,1,1); else lv_spell:=lv_spell||lv_temp; end if; elsif lengthb(lv_char) = 3 then --Select replace(substrb(dump(convert(lv_char,'ZHS16GBK','UTF8'),1010),instrb(dump(convert(lv_char,'ZHS16GBK','UTF8'),1010),'UTF8:')),'UTF8: ','') --Into lv_bytes from dual; --li_pos:=instr(lv_bytes,','); --li_bytes:=substr(lv_bytes,1,li_pos-1)*256+substr(lv_bytes,li_pos+1)-256*256; Select ascii(lv_char)-256*256 Into li_bytes From dual; select max(spell) Into lv_temp from table(f_getSpellcode) where code<=li_bytes; if p_sign is null then lv_spell:=lv_spell||substr(lv_char,1,1); else lv_spell:=lv_spell||lv_char; end if; end if; end loop; return lv_spell; end; --hanjs,07-10-24,此函数默认返回汉字拼音的首字母，第二个参数不为空则返回全拼。