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

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

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

--获取单个汉字的首字
CREATE FUNCTION fGetPyChar (@iHanZi  VARCHAR(2)) RETURNS VARCHAR(10)
AS
  BEGIN
    DECLARE  @sRet VARCHAR(10)
    DECLARE  @nHanZi  INT
SET @nHanZi = ASCII(SUBSTRING(@iHanZi,1,1))*257
IF( (@nHanZi>=dbo.fHexToINT('B0A1')) AND( @nHanZi<=dbo.
fHexToINT('B0C4')) )
               SET @sRet = 'A'
IF @nHanZi>=dbo.fHexToINT('B0C5') AND @nHanZi<=dbo.fHexToINT('B2C0')
           SET @sRet = 'B'
IF @nHanZi>=dbo.fHexToINT('B2C1') AND @nHanZi<=dbo.fHexToINT('B4ED')
           SET @sRet = 'C'
IF @nHanZi>=dbo.fHexToINT('B4EE') AND @nHanZi<=dbo.fHexToINT('B6E9')
           SET @sRet = 'D'
IF @nHanZi>=dbo.fHexToINT('B6EA') AND @nHanZi<=dbo.fHexToINT('B7A1')
           SET @sRet = 'E'
IF @nHanZi>=dbo.fHexToINT('B7A2') AND @nHanZi<=dbo.fHexToINT('B8C0')
           SET @sRet = 'F'
IF @nHanZi>=dbo.fHexToINT('B8C1') AND @nHanZi<=dbo.fHexToINT('B9FD')
           SET @sRet = 'G'
IF @nHanZi>=dbo.fHexToINT('B9FE') AND @nHanZi<=dbo.fHexToINT('BBF6')
           SET @sRet = 'H'
IF @nHanZi>=dbo.fHexToINT('BBF7') AND @nHanZi<=dbo.fHexToINT('BFA5')
           SET @sRet = 'J'
IF @nHanZi>=dbo.fHexToINT('BFA6') AND @nHanZi<=dbo.fHexToINT('C0AB')
           SET @sRet = 'K'
IF @nHanZi>=dbo.fHexToINT('C0AC') AND @nHanZi<=dbo.fHexToINT('C2E7')
           SET @sRet = 'L'
IF @nHanZi>=dbo.fHexToINT('C2E8') AND @nHanZi<=dbo.fHexToINT('C4C2')
           SET @sRet = 'M'
IF @nHanZi>=dbo.fHexToINT('C4C3') AND @nHanZi<=dbo.fHexToINT('C5B5')
           SET @sRet = 'N'
IF @nHanZi>=dbo.fHexToINT('C5B6') AND @nHanZi<=dbo.fHexToINT('C5BD')
           SET @sRet = 'O'
IF @nHanZi>=dbo.fHexToINT('C5BE') AND @nHanZi<=dbo.fHexToINT('C6D9')
           SET @sRet = 'P'
IF @nHanZi>=dbo.fHexToINT('C6DA') AND @nHanZi<=dbo.fHexToINT('C8BA')
           SET @sRet = 'Q'
IF @nHanZi>=dbo.fHexToINT('C8BB') AND @nHanZi<=dbo.fHexToINT('C8F5')
           SET @sRet = 'R'
IF @nHanZi>=dbo.fHexToINT('C8F6') AND @nHanZi<=dbo.fHexToINT('CBF9')
           SET @sRet = 'S'
IF @nHanZi>=dbo.fHexToINT('CBFA') AND @nHanZi<=dbo.fHexToINT('CDD9')
           SET @sRet = 'T'
IF @nHanZi>=dbo.fHexToINT('CDDA') AND @nHanZi<=dbo.fHexToINT('CEF3')
           SET @sRet = 'W'
IF @nHanZi>=dbo.fHexToINT('CEF4') AND @nHanZi<=dbo.fHexToINT('D1B8')
           SET @sRet = 'X'
IF @nHanZi>=dbo.fHexToINT('D1B9') AND @nHanZi<=dbo.fHexToINT('D4D0')
           SET @sRet = 'Y'
IF @nHanZi>=dbo.fHexToINT('D4D1') AND @nHanZi<=dbo.fHexToINT('D7F9')
           SET @sRet = 'Z'
  RETURN( @sRet )
END
--获取汉字词组的拼音首字
CREATE FUNCTION fGetPyStr(@iHanzi  VARCHAR(40)) RETURNS VARCHAR(20)
AS
BEGIN
  DECLARE @sRet VARCHAR(20)
  DECLARE @nBegin INT
  DECLARE @nHanZi INT
SET @sRet = ''
SET @nBegin = 1
while @nBegin<=LEN(@iHanzi)
               BEGIN
  SET @nHanZi = ASCII(SUBSTRING(@iHanzi,@nBegin,1))*257
  IF @nHanZi >= dbo.fHexToINT('B0A1')
                      BEGIN
   SET @sRet = @sRet + dbo.fGetPyChar(SUBSTRING(@iHanzi,@nBegin,1 ))
   SET @nBegin = @nBegin + 1
                      END
  ELSE
                      BEGIN
   SET @sRet = @sRet+UPPER(SUBSTRING(@iHanzi,@nBegin,1 ) )
   SET @nBegin = @nBegin + 1
                      END
                CONTINUE
               END
  RETURN(@sRet)
END
--将16进制转化成10进制
CREATE FUNCTION fHexToINT ( @iHex VARCHAR(20) )  RETURNS  INT
AS
BEGIN
     DECLARE @sChar VARCHAR
     DECLARE @nNum INT
     DECLARE @nHex INT
     DECLARE @I INT
        SET @nNum = 0
        SET @I=1
WHILE @I<=LEN(@iHex)
             BEGIN
  SET @sChar = SUBSTRING(UPPER(@iHex),@i,1)
     IF @sChar<='9'
                        BEGIN
                          SET @nHex = CAST((@sChar) AS INT)
                        END
           ELSE
                          BEGIN
     SET @nHex = ASCII(@sChar) - 55
                            END
           SET @nNum = @nNum * 16 + @nHex
                    SET @I=@I+1
     CONTINUE
              END
     Return( @nNum)
     END

--
     来人
      Welcome to Sunrise!

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

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

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

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