生成汉字助记码

ALTER FUNCTION getPinYin(@HZ NVARCHAR(4000))
RETURNS NVARCHAR(4000)
AS
BEGIN
DECLARE @word NCHAR(1)
DECLARE @PY NVARCHAR(4000)
SET @PY = ''
WHILE LEN(@HZ) > 0
  BEGIN
   SET @word = LEFT(@HZ,1)
   SET @PY = @PY + (CASE WHEN UNICODE(@word) BETWEEN 19968 AND 19968+20901
                        THEN (SELECT  TOP 1 PY
                              FROM ( select 'A' as PY,N'驁' as word
          union all select 'B',N'簿'
          union all select 'C',N'錯'
          union all select 'D',N'鵽'
          union all select 'E',N'樲'
          union all select 'F',N'鰒'
          union all select 'G',N'腂'
          union all select 'H',N'夻'
          union all select 'J',N'攈'
          union all select 'K',N'穒'
          union all select 'L',N'鱳'
          union all select 'M',N'旀'
          union all select 'N',N'桛'
          union all select 'O',N'漚'
          union all select 'P',N'曝'
          union all select 'Q',N'囕'
          union all select 'R',N'鶸'
          union all select 'S',N'蜶'
          union all select 'T',N'籜'
          union all select 'W',N'鶩'
          union all select 'X',N'鑂'
          union all select 'Y',N'韻'
          union all select 'Z',N'咗'
                                   ) T 
                               WHERE  word>=@word collate Chinese_PRC_CS_AS_KS_WS
                               order by PY ASC)
                           ELSE @word
                           END
                            )
     SET @HZ = RIGHT(@HZ,LEN(@HZ)-1)
  END
  RETURN @PY
END

 

转载于:https://www.cnblogs.com/armanda/p/3611087.html