SQL Server 中文拼音首字母模糊查詢函數實現

需求場景

用戶輸入「zgyh」能查出「中國銀行」，輸入「dg」匹配「東莞」。核心需求：將漢字轉換為拼音首字母，支持模糊匹配。

核心函數：f_GetPy

CREATE FUNCTION f_GetPy(@Str NVARCHAR(400))
RETURNS NVARCHAR(4000)
AS
BEGIN
    DECLARE @strlen INT, @re NVARCHAR(4000);
    DECLARE @t TABLE (chr NCHAR(1) COLLATE Chinese_PRC_CI_AS, letter NCHAR(1));

    -- 拼音區間映射表 (GB2312 編碼順序)
    INSERT @t VALUES
    ('吖','A'),('八','B'),('嚓','C'),('咑','D'),('妸','E'),('發','F'),
    ('旮','G'),('铪','H'),('丌','J'),('咔','K'),('垃','L'),('嘸','M'),
    ('拏','N'),('噢','O'),('妑','P'),('七','Q'),('呥','R'),('仨','S'),
    ('他','T'),('屲','W'),('夕','X'),('丫','Y'),('帀','Z');

    SELECT @strlen = LEN(@Str), @re = '';
    WHILE @strlen > 0
    BEGIN
        -- 從映射表查找最大不超過當前字符的拼音首字母
        SELECT TOP 1 @re = letter + @re, @strlen = @strlen - 1
        FROM @t
        WHERE chr <= SUBSTRING(@Str, @strlen, 1)
        ORDER BY chr DESC;

        IF @@ROWCOUNT = 0
            -- 非漢字字符直接保留
            SELECT @re = SUBSTRING(@Str, @strlen, 1) + @re, @strlen = @strlen - 1;
    END
    RETURN @re;
END

測試驗證

SELECT dbo.f_GetPy('東莞市') AS DG;      -- 返回 'DG'
SELECT dbo.f_GetPy('ab中c國人') AS ZGR;  -- 返回 'abZGcR'

業務查詢應用

-- 查詢名稱拼音首字母包含 'zgyh' 的記錄
SELECT * FROM Customer
WHERE dbo.f_GetPy(Name) LIKE '%ZGYH%';

-- 或創建計算列+索引優化
ALTER TABLE Customer ADD PyInitials AS dbo.f_GetPy(Name);
CREATE INDEX IX_Customer_PyInitials ON Customer(PyInitials);

性能優化建議

• 函數為標量 UDF，大表查詢會導致全表掃描，**必須創建計算列並建索引**
• 映射表 @t 可改為永久表 PinyinMap，避免每次調用重建表變量
• 對於高頻查詢，考慮在應用層預計算拼音首字母存入冗余列
• MySQL 環境可用 CONVERT(name USING gbk) 配合自定義函數實現類似邏輯

擴展：全拼音支持

若需全拼音（如「北京」→「beijing」），需引入完整拼音字典表（約 2 萬漢字），邏輯相似但映射表大幅擴大，建議使用成熟開源庫（如 pinyin4j Java、xpinyin Python）在應用層處理。