oracle 汉字转拼音函数 google
转自:http://www.cnblogs.com/zfc2201/archive/2012/04/16/2452751.html
http://aspxuexi.blog.hexun.com/77579695_d.html
http://zhouyundong0482.blog.163.com/blog/static/103517920085210210604/
根据姓名获得该姓名的拼音简码,如姓名为刘红,根据该函数获得的拼音为lh,该函数主要用户在查询时,可根据姓名的拼写简码进行查询
数据库字符集为GBK:
- create or replace
- function PY (P1 VARCHAR2,P2 INT default 0) return VARCHAR2 as
- CHAR1 VARCHAR2(2);
- CHAR2 VARCHAR2(2);
- LEN INTEGER;
- N1 INTEGER;
- N2 INTEGER;
- CSOUND VARCHAR2(32767);
- BEGIN
- IF P2 = 1 THEN
- LEN := 1;
- ELSE
- LEN := LENGTH(P1);
- END IF;
- FOR I IN 1..LEN LOOP
- CHAR2 := SUBSTR(P1, i, 1);
- N1 := FLOOR(ASCII(CHAR2) / 256);
- N2 := MOD(ASCII(CHAR2), 256);
- IF N1 = 0 AND N2 <= 129 THEN
- CHAR1 := CHAR2;
- ELSIF N2 <> 127 AND N2 <> 255 AND NOT (N2 >= 0 AND N2 <= 63) THEN
- SELECT
- CASE
- WHEN CHAR2 >= '丂' AND CHAR2 < '芭' THEN 'A'
- WHEN CHAR2 >= '芭' AND CHAR2 < '擦' THEN 'B'
- WHEN CHAR2 >= '擦' AND CHAR2 < '搭' THEN 'C'
- WHEN CHAR2 >= '搭' AND CHAR2 < '蛾' THEN 'D'
- WHEN CHAR2 >= '蛾' AND CHAR2 < '发' THEN 'E'
- WHEN CHAR2 >= '发' AND CHAR2 < '噶' THEN 'F'
- WHEN CHAR2 >= '噶' AND CHAR2 < '哈' THEN 'G'
- WHEN CHAR2 >= '哈' AND CHAR2 < '击' THEN 'H'
- WHEN CHAR2 >= '击' AND CHAR2 < '喀' THEN 'J'
- WHEN CHAR2 >= '喀' AND CHAR2 < '垃' THEN 'K'
- WHEN CHAR2 >= '垃' AND CHAR2 < '妈' THEN 'L'
- WHEN CHAR2 >= '妈' AND CHAR2 < '拿' THEN 'M'
- WHEN CHAR2 >= '拿' AND CHAR2 < '哦' THEN 'N'
- WHEN CHAR2 >= '哦' AND CHAR2 < '啪' THEN 'O'
- WHEN CHAR2 >= '啪' AND CHAR2 < '期' THEN 'P'
- WHEN CHAR2 >= '期' AND CHAR2 < '然' THEN 'Q'
- WHEN CHAR2 >= '然' AND CHAR2 < '撒' THEN 'R'
- WHEN CHAR2 >= '撒' AND CHAR2 < '塌' THEN 'S'
- WHEN CHAR2 >= '塌' AND CHAR2 < '挖' THEN 'T'
- WHEN CHAR2 >= '挖' AND CHAR2 < '稀' THEN 'W'
- WHEN CHAR2 >= '稀' AND CHAR2 < '压' THEN 'X'
- WHEN CHAR2 >= '压' AND CHAR2 < '匝' THEN 'Y'
- WHEN CHAR2 >= '匝' AND CHAR2 <= '鼱' THEN 'Z'
- END
- INTO CHAR1
- FROM DUAL;
- ELSE
- CHAR1 := 'ER';
- END IF;
- CSOUND := CSOUND || CHAR1;
- END LOOP;
- RETURN CSOUND;
- END;
如果数据库字符集为UTF-8,则函数如下:
- create or replace
- FUNCTION GETCH2SPELL(P_NAME IN VARCHAR2) RETURN VARCHAR2 AS
- V_COMPARE VARCHAR2(100);
- V_RETURN VARCHAR2(4000);
- CHAR1 VARCHAR2(3);
- CHAR2 VARCHAR2(3);
- LEN INTEGER;
- N1 INTEGER;
- N2 INTEGER;
- FUNCTION F_NLSSORT(P_WORD IN VARCHAR2) RETURN VARCHAR2 AS
- BEGIN
- RETURN NLSSORT(P_WORD, 'NLS_SORT=SCHINESE_PINYIN_M');
- END;
- BEGIN
- LEN := LENGTH(P_NAME);
- FOR I IN 1..LEN LOOP
- CHAR2 := SUBSTR(P_NAME, i, 1);
- N1 := FLOOR(ASCII(CHAR2) / 256);
- N2 := MOD(ASCII(CHAR2), 256);
- IF N1 = 0 AND N2 <= 129 THEN
- V_RETURN := V_RETURN || CHAR2;
- ELSIF N2 <> 127 AND N2 <> 255 AND NOT (N2 >= 0 AND N2 <= 63) THEN
- V_COMPARE := F_NLSSORT(SUBSTR(P_NAME, I, 1));
- IF V_COMPARE >= F_NLSSORT(' 吖 ') AND V_COMPARE <= F_NLSSORT('驁 ') THEN
- V_RETURN := V_RETURN || 'A';
- ELSIF V_COMPARE >= F_NLSSORT('八 ') AND V_COMPARE <= F_NLSSORT('簿 ') THEN
- V_RETURN := V_RETURN || 'B';
- ELSIF V_COMPARE >= F_NLSSORT('嚓 ') AND V_COMPARE <= F_NLSSORT('錯 ') THEN
- V_RETURN := V_RETURN || 'C';
- ELSIF V_COMPARE >= F_NLSSORT('咑 ') AND V_COMPARE <= F_NLSSORT('鵽 ') THEN
- V_RETURN := V_RETURN || 'D';
- ELSIF V_COMPARE >= F_NLSSORT('妸 ') AND V_COMPARE <= F_NLSSORT('樲 ') THEN
- V_RETURN := V_RETURN || 'E';
- ELSIF V_COMPARE >= F_NLSSORT('发 ') AND V_COMPARE <= F_NLSSORT('猤 ') THEN
- V_RETURN := V_RETURN || 'F';
- ELSIF V_COMPARE >= F_NLSSORT('旮 ') AND V_COMPARE <= F_NLSSORT('腂 ') THEN
- V_RETURN := V_RETURN || 'G';
- ELSIF V_COMPARE >= F_NLSSORT('妎 ') AND V_COMPARE <= F_NLSSORT('夻 ') THEN
- V_RETURN := V_RETURN || 'H';
- ELSIF V_COMPARE >= F_NLSSORT('丌 ') AND V_COMPARE <= F_NLSSORT('攈 ') THEN
- V_RETURN := V_RETURN || 'J';
- ELSIF V_COMPARE >= F_NLSSORT('咔 ') AND V_COMPARE <= F_NLSSORT('穒 ') THEN
- V_RETURN := V_RETURN || 'K';
- ELSIF V_COMPARE >= F_NLSSORT('垃 ') AND V_COMPARE <= F_NLSSORT('擽 ') THEN
- V_RETURN := V_RETURN || 'L';
- ELSIF V_COMPARE >= F_NLSSORT('嘸 ') AND V_COMPARE <= F_NLSSORT('椧 ') THEN
- V_RETURN := V_RETURN || 'M';
- ELSIF V_COMPARE >= F_NLSSORT('拏 ') AND V_COMPARE <= F_NLSSORT('瘧 ') THEN
- V_RETURN := V_RETURN || 'N';
- ELSIF V_COMPARE >= F_NLSSORT('筽 ') AND V_COMPARE <= F_NLSSORT('漚 ') THEN
- V_RETURN := V_RETURN || 'O';
- ELSIF V_COMPARE >= F_NLSSORT('妑 ') AND V_COMPARE <= F_NLSSORT('曝 ') THEN
- V_RETURN := V_RETURN || 'P';
- ELSIF V_COMPARE >= F_NLSSORT('七 ') AND V_COMPARE <= F_NLSSORT('裠 ') THEN
- V_RETURN := V_RETURN || 'Q';
- ELSIF V_COMPARE >= F_NLSSORT('亽 ') AND V_COMPARE <= F_NLSSORT('鶸 ') THEN
- V_RETURN := V_RETURN || 'R';
- ELSIF V_COMPARE >= F_NLSSORT('仨 ') AND V_COMPARE <= F_NLSSORT('蜶 ') THEN
- V_RETURN := V_RETURN || 'S';
- ELSIF V_COMPARE >= F_NLSSORT('侤 ') AND V_COMPARE <= F_NLSSORT('籜 ') THEN
- V_RETURN := V_RETURN || 'T';
- ELSIF V_COMPARE >= F_NLSSORT('屲 ') AND V_COMPARE <= F_NLSSORT('鶩 ') THEN
- V_RETURN := V_RETURN || 'W';
- ELSIF V_COMPARE >= F_NLSSORT('夕 ') AND V_COMPARE <= F_NLSSORT('鑂 ') THEN
- V_RETURN := V_RETURN || 'X';
- ELSIF V_COMPARE >= F_NLSSORT('丫 ') AND V_COMPARE <= F_NLSSORT('韻 ') THEN
- V_RETURN := V_RETURN || 'Y';
- ELSIF V_COMPARE >= F_NLSSORT('帀 ') AND V_COMPARE <= F_NLSSORT('咗 ') THEN
- V_RETURN := V_RETURN || 'Z';
- END IF;
- END IF;
- END LOOP;
- RETURN V_RETURN;
- END;
/*
作者:chensq@itpub.net
日期: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<'芭' then 'A'
when c2>='芭' and c2<'擦' then 'B'
when c2>='擦' and c2<'搭' then 'C'
when c2>='搭' and c2<'蛾' then 'D'
when c2>='蛾' and c2<'发' then 'E'
when c2>='发' and c2<'噶' then 'F'
when c2>='噶' and c2<'哈' then 'G'
when c2>='哈' and c2<'击' then 'H'
when c2>='击' and c2<'喀' then 'J'
when c2>='喀' and c2<'垃' then 'K'
when c2>='垃' and c2<'妈' then 'L'
when c2>='妈' and c2<'拿' then 'M'
when c2>='拿' and c2<'哦' then 'N'
when c2>='哦' and c2<'啪' then 'O'
when c2>='啪' and c2<'期' then 'P'
when c2>='期' and c2<'然' then 'Q'
when c2>='然' and c2<'撒' then 'R'
when c2>='撒' and c2<'塌' then 'S'
when c2>='塌' and c2<'挖' then 'T'
when c2>='挖' and c2<'稀' then 'W'
when c2>='稀' and c2<'压' then 'X'
when c2>='压' and c2<'匝' then 'Y'
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('中国人用自己的sound()函数',2) from dual ;
CSOUND('中国人用自己的SOUND()
----------------------------------------------------------------
ZGRYZJDsound()HS
SQL> select csound('台海分离美国得利',2) from dual ;
CSOUND('台海分离美国得利',2)
---------------------------------------------------------------
THFLMGDL
SQL> select csound('热烈庆祝财富论坛圆满召开',2) from dual ;
CSOUND('热烈庆祝财富论坛圆满召
---------------------------------------------------------------
RLQZCFLTYMZK
========================
create or replace function csound(c in varchar2,op in integer default 1)
return varchar2
/*
修正说明:修正了前一版本按字符编面为序的算法,改为按简体中文拼音拼序,非文字字符以~表示。
修正日期:2005-05-19 22:30
特别感谢:yangtingkun、rollingpig、 black_dragon 等
作者:chensq@itpub.net
日期:2005-05-18 22:30
说明:此函数用于将汉字转换为其发音的首字母,对于ascii码值在1~128间的字符不作任何处理,其它字符以.代替。
用法:默认情况下,只对第一个字符进行转换,如果加上不等于1的参数,则对全字符串进行处理,支持中英文合编。
示例:
SQL> select csound('中国人用自己的sound()函数,''岜''不再转换为z了',2) from dual;
CSOUND('中国人用自己的SOUND()
--------------------------------------------------------------------------------
ZGRYZJDsound()HS,'B'BZZHWzL
SQL> select csound('中国人用自己的sound()函数,''岜''不再转换为z了') from dual ;
CSOUND('中国人用自己的SOUND()
--------------------------------------------------------------------------------
Z
备注:此函数只在ZHS16GBK字符集的Oracle 9.2中通过,其它平台没有测试,请根据实际需要作相应的修改。
*/
is
c1 varchar2(2);
c2 varchar2(2);
p integer;
n1 integer;
n2 integer;
n3 integer;
csound varchar2(4000);
c3 varchar2(100):='A吖B八C嚓D咑E妸F发G旮H铪J讥K咔L垃M嘸N拏O筽P妑Q七R罖S仨T侤W屲X夕Y丫Z帀~';
c4 varchar2(120);
c5 varchar2(2);
begin
if op=1 then p:=1 ;
else p:=length(c);
end if;
for i in 1..p
loop
c1:='';
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
n3:=instr(c3,c2,1,1);
if n3=0 then
c4:='';
for i in (
select c3
from (
select '吖' c3 from dual
union select '八' from dual
union select '嚓' from dual
union select '咑' from dual
union select '妸' from dual
union select '发' from dual
union select '旮' from dual
union select '铪' from dual
union select '讥' from dual
union select '咔' from dual
union select '垃' from dual
union select '嘸' from dual
union select '拏' from dual
union select '筽' from dual
union select '妑' from dual
union select '七' from dual
union select '罖' from dual
union select '仨' from dual
union select '侤' from dual
union select '屲' from dual
union select '夕' from dual
union select '丫' from dual
union select '帀' from dual
union select c2 from dual)
order by nlssort(c3,'NLS_SORT=SCHINESE_PINYIN_M'))
loop
c4:=c4||i.c3;
end loop;
c5:=substr(c4,instr(c4,c2,1,1)-1,1);
n3:=instr(c3,c5,1,1);
end if;
c1:=substr(c3,n3-1,1);
else
c1:='.';
end if;
csound:=csound||c1;
end loop;
return csound;
end;