● [原创]汉字拼音首字检索-Oracle(转载) - 荔园在线 | 深圳大学学生论坛,荔园晨风BBS,深大在线网站

荔园之美，在春之萌芽，在夏之绽放，在秋之收获，在冬之沉淀

发信人: baty (新一代懒人), 信区: Database
标  题: [原创]汉字拼音首字检索-Oracle(转载)
发信站: 荔园晨风BBS站 (Thu Oct 25 23:02:40 2001), 转信

--转化16进制到10进制

CREATE OR REPLACE FUNCTION fHexToNumber( iHex IN VARCHAR2 ) RETURN
NUMBER IS

sChar VARCHAR2(1);
nNum NUMBER;
nHex NUMBER;
BEGIN
nNum := 0;
FOR i IN 1..LENGTHB(iHex) LOOP
  sChar := SUBSTRB(UPPER(iHex),i,1);
  IF sChar<='9' THEN nHex := TO_NUMBER(sChar);
  ELSE
   nHex := ASCII(sChar) - 55;
  END IF;
  nNum := nNum * 16 + nHex;
END LOOP;
Return( nNum);
END;
/

--将单个汉字转化成拼音首字
CREATE OR REPLACE FUNCTION fGetPyChar(
  iHanZi IN VARCHAR2 ) RETURN VARCHAR2 IS
sRet VARCHAR2(1);
nHanZi NUMBER;
BEGIN
nHanZi := ASCII(SUBSTRB(iHanzi,1,1))*256 + ASCII(SUBSTRB(iHanzi,2,1));

IF nHanZi>=fHexToNumber('B0A1') AND nHanZi<=fHexToNumber('B0C4') THEN
sRet
:= 'A'; END IF;
IF nHanZi>=fHexToNumber('B0C5') AND nHanZi<=fHexToNumber('B2C0') THEN
sRet
:= 'B'; END IF;
IF nHanZi>=fHexToNumber('B2C1') AND nHanZi<=fHexToNumber('B4ED') THEN
sRet
:= 'C'; END IF;
IF nHanZi>=fHexToNumber('B4EE') AND nHanZi<=fHexToNumber('B6E9') THEN
sRet
:= 'D'; END IF;
IF nHanZi>=fHexToNumber('B6EA') AND nHanZi<=fHexToNumber('B7A1') THEN
sRet
:= 'E'; END IF;
IF nHanZi>=fHexToNumber('B7A2') AND nHanZi<=fHexToNumber('B8C0') THEN
sRet
:= 'F'; END IF;
IF nHanZi>=fHexToNumber('B8C1') AND nHanZi<=fHexToNumber('B9FD') THEN
sRet
:= 'G'; END IF;
IF nHanZi>=fHexToNumber('B9FE') AND nHanZi<=fHexToNumber('BBF6') THEN
sRet
:= 'H'; END IF;
IF nHanZi>=fHexToNumber('BBF7') AND nHanZi<=fHexToNumber('BFA5') THEN
sRet
:= 'J'; END IF;
IF nHanZi>=fHexToNumber('BFA6') AND nHanZi<=fHexToNumber('C0AB') THEN
sRet
:= 'K'; END IF;
IF nHanZi>=fHexToNumber('C0AC') AND nHanZi<=fHexToNumber('C2E7') THEN
sRet
:= 'L'; END IF;
IF nHanZi>=fHexToNumber('C2E8') AND nHanZi<=fHexToNumber('C4C2') THEN
sRet
:= 'M'; END IF;
IF nHanZi>=fHexToNumber('C4C3') AND nHanZi<=fHexToNumber('C5B5') THEN
sRet
:= 'N'; END IF;
IF nHanZi>=fHexToNumber('C5B6') AND nHanZi<=fHexToNumber('C5BD') THEN
sRet
:= 'O'; END IF;
IF nHanZi>=fHexToNumber('C5BE') AND nHanZi<=fHexToNumber('C6D9') THEN
sRet
:= 'P'; END IF;
IF nHanZi>=fHexToNumber('C6DA') AND nHanZi<=fHexToNumber('C8BA') THEN
sRet
:= 'Q'; END IF;
IF nHanZi>=fHexToNumber('C8BB') AND nHanZi<=fHexToNumber('C8F5') THEN
sRet
:= 'R'; END IF;
IF nHanZi>=fHexToNumber('C8F6') AND nHanZi<=fHexToNumber('CBF9') THEN
sRet
:= 'S'; END IF;
IF nHanZi>=fHexToNumber('CBFA') AND nHanZi<=fHexToNumber('CDD9') THEN
sRet
:= 'T'; END IF;
IF nHanZi>=fHexToNumber('CDDA') AND nHanZi<=fHexToNumber('CEF3') THEN
sRet
:= 'W'; END IF;
IF nHanZi>=fHexToNumber('CEF4') AND nHanZi<=fHexToNumber('D1B8') THEN
sRet
:= 'X'; END IF;
IF nHanZi>=fHexToNumber('D1B9') AND nHanZi<=fHexToNumber('D4D0') THEN
sRet
:= 'Y'; END IF;
IF nHanZi>=fHexToNumber('D4D1') AND nHanZi<=fHexToNumber('D7F9') THEN
sRet
:= 'Z'; END IF;
  RETURN( sRet );
END;
/

--将汉字词组转化成拼音首字
CREATE OR REPLACE FUNCTION fGetPyStr(
  iHanZi IN VARCHAR2 ) RETURN VARCHAR2 IS
sRet VARCHAR2(4000);
sChar VARCHAR2(2);
nBegin NUMBER;
nHanZi NUMBER;
BEGIN
sRet := '';
nBegin := 1;
while nBegin<=LENGTHB(iHanZi) LOOP
  nHanZi := ASCII(SUBSTRB(iHanzi,nBegin,1))*256 + ASCII(SUBSTRB(iHanzi,
nBegi
n+1,1));
  IF nHanZi >= fHexToNumber('B0A1') THEN
   sRet := sRet || fGetPyChar(SUBSTRB(iHanzi,nBegin,2 ));
   nBegin := nBegin + 2;
  ELSE
--   sRet := sRet || SUBSTRB(iHanzi,nBegin,1 );
   nBegin := nBegin + 1;
  END IF;
END LOOP;
  RETURN( sRet );
END;

--
     来人
      Welcome to Sunrise!

             我总有一种想为你而死的冲动

                          因为我不知如何才能把你打动

※ 来源:·荔园晨风BBS站 bbs.szu.edu.cn·[FROM: 192.168.1.242]

荔园在线首页友情链接：深圳大学深大招生荔园晨风BBS S-Term软件网络书店