oracle中文转全拼音,(转)oracle下,把中文转为拼音函数编写

(转)oracle下，把中文转为拼音函数编写

作者：chensq@http://www.doczj.com/doc/cf8b56db3968011ca20091a6.html

日期：2005-05-18 22:30

说明：此函数用于将汉字转换为其发音的首字母，对于ascii码值在1～128间的字符不作任何处理，其它字符返回错误信息。

用法：默认情况下，只对第一个字符进行转换，如果加上不等于1的参数，则对全字符串进行处理，支持中英文合编。如：

SQL> select csound('中国人用自己的sound()函数',2) from dual ;

CSOUND('中国人用自己的SOUND()

-------------------------------------------------------------

ZGRYZJDsound()HS

SQL> select csound('中国人用自己的sound()函数') from dual ;

CSOUND('中国人用自己的SOUND()

--------------------------------------------------------------

Z

备注：此函数只在ZHS16GBK字符集的Oracle 9.2，Oracle 10g中通过，其它平台没有测试，请根据实际需要作相应的修改。

*/

create or replace function csound(c in varchar2,

op in integer default 1

)

return varchar2

is

c1 varchar2(2);

c2 varchar2(2);

p integer;

n1 integer;

n2 integer;

csound varchar2(32767);

begin

if op=1 then p:=1 ;

else p:=length(c);

end if;

for i in 1..p

loop

c2:=substr(c,i,1);

n1:=floor(ascii(c2)/256);

n2:=mod(ascii(c2),256);

if n1=0 and n2<=129 then

c1:=c2;

elsif n2<>127 and n2<>255

and not (n2 between 0 and 63) then

select

case

when c2>='丂' and c2

when c2>='芭' and c2

when c2>='擦' and c2

when c2>='搭' and c2

when c2>='蛾' and c2

when c2>='发' and c2

when c2>='噶' and c2

when c2>='哈' and c2

when c2>='击' and c2

when c2>='喀' and c2

when c2>='垃' and c2

when c2>='妈' and c2

when c2>='拿' and c2

when c2>='哦' and c2

when c2>='啪' and c2

when c2>='期' and c2

when c2>='然' and c2

when c2>='撒' and c2

when c2>='塌' and c2

when c2>='挖' and c2

when c2>='稀' and c2

when c2>='压' and c2

when c2>='匝' and c2<='鼱' then 'Z' end

into c1

from dual;

else

c1:='er';

end if;

csound:=csound||c1;

end loop;

return csound;

end;

SQL> select csound('中国人用自己