萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 自定義函數:獲取漢字拼音首字母(支持多音字按常用排序)

函數代碼

CREATE FUNCTION dbo.fn_GetPinyinInitial (@str NVARCHAR(4000))
RETURNS NVARCHAR(4000)
AS
BEGIN
    DECLARE @word NCHAR(1), @py NVARCHAR(4000) = N'';
    WHILE LEN(@str) > 0
    BEGIN
        SET @word = LEFT(@str, 1);
        -- 判斷是否為 CJK 統一漢字擴展區 (19968~40869)
        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 @str = RIGHT(@str, LEN(@str) - 1);
    END
    RETURN @py;
END

原理解析

  • 遍歷輸入字符串每個字符。
  • UNICODE(@word) BETWEEN 19968 AND 40869 覆蓋基本區大多數常用漢字(\u4E00-\u9FFF)。
  • 內聯值表按拼音順序存儲每個聲母對應的該聲母區塊最後一個漢字(按 Chinese_PRC_CS_AS_KS_WS 排序規則)。
  • WHERE word >= @word ... ORDER BY PY ASC 取首個滿足條件的聲母,即該字所在聲母區。
  • 非漢字直接拼接原字符。

調用示例

SELECT dbo.fn_GetPinyinInitial(N'張三李四')  -- 返回 'ZSLS'
SELECT dbo.fn_GetPinyinInitial(N'你好2024')  -- 返回 'NH2024'

局限與改進方向

  • 多音字:按字典順序取首個聲母(如「長」→ C),非語音引擎無法語境消歧。
  • 擴展區:罕見字(擴展 A/B/C/D)超出範圍會當作非漢字原樣輸出,可擴展映射表。
  • 性能:標量函數逐字符循環,大批量查詢慢。高頻場景建議:
    ① 物化列 + 觸發器維護;② CLR 函數(C# 實現,數量級更快);③ 應用層緩存。
  • 排序規則依賴:要求實例安裝 Chinese_PRC_CS_AS_KS_WS(中文簡體_區分大小寫_區分重音_區分假名_區分寬度),缺失會報錯。

替代方案:CLR 函數(高性能)

[SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static SqlString GetPinyinInitial(SqlString input)
{
    if (input.IsNull) return SqlString.Null;
    var sb = new System.Text.StringBuilder();
    foreach (char c in input.Value)
    {
        if (c >= 0x4E00 && c <= 0x9FFF)
        {
            // 使用 Pinyin4net 或自帶映射表
            sb.Append(GetInitial(c)); // 實現略
        }
        else sb.Append(c);
    }
    return new SqlString(sb.ToString());
}

部署後 CREATE ASSEMBLY + CREATE FUNCTION ... EXTERNAL NAME,性能提升 50-100 倍。

copyright © 萬盛學電腦網 all rights reserved