SQLServer 获取汉字字符串对应的拼音字母字符串

当需要通过汉字的拼音字母对汉字进行排序时会用到汉字到拼音的转换，此函数就是实现这个功能，常见场景为通讯录 。实现方式为SQL Server 语句获取中文字符串对应的汉语拼音字符串。

IF(OBJECT_ID('GetPinyinLetter') IS NOT null)
	DROP FUNCTION GetPyLetter;
GO 

CREATE FUNCTION GetPinyinLetter
    (
      @ChineseStr NVARCHAR(4000)
    )
RETURNS NVARCHAR(4000)
AS
    BEGIN
 
        DECLARE @SingleChar NCHAR(1) ,
            @ReturnString NVARCHAR(4000); 
        SET @ReturnString = ''; 

        WHILE LEN(@ChineseStr) > 0
            BEGIN
                SET @SingleChar = LEFT(@ChineseStr, 1); 
                IF ( UNICODE(@SingleChar) BETWEEN 19968 AND 19968 + 20901 )
                    SET @ReturnString = @ReturnString
                        + ( SELECT TOP 1
                                    PY
                            FROM    ( SELECT    'A' AS PY ,
                                                N'骜' AS ChineseCharacters
                                      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'咗'
                                    ) SpellingTable
                            WHERE   ChineseCharacters > = @SingleChar COLLATE Chinese_PRC_CS_AS_KS_WS
                            ORDER BY PY ASC
                          );
                ELSE
                    SET @ReturnString = @ReturnString + @SingleChar;

 
                SET @ChineseStr = RIGHT(@ChineseStr, LEN(@ChineseStr) - 1); 

            END;
 
        RETURN @ReturnString; 

    END;
GO