SQL Server 自動產生漢字拼音首字母函數 fn_GetPy

函數用途

輸入中文字串，輸出對應的拼音首字母小寫字串（如「中國」→ zg），常用於下拉選單快速篩選、索引建立、搜尋提示等場景。

完整函數建立腳本

CREATE FUNCTION fn_GetPy(@str NVARCHAR(4000))
RETURNS NVARCHAR(4000)
AS
BEGIN
    DECLARE @intLen INT = LEN(@str);
    DECLARE @strRet NVARCHAR(4000) = '';
    DECLARE @temp NVARCHAR(100);

    WHILE @intLen > 0
    BEGIN
        SET @temp = '';
        SELECT @temp = CASE
            WHEN SUBSTRING(@str, @intLen, 1) >= N'帀' THEN 'Z'
            WHEN SUBSTRING(@str, @intLen, 1) >= N'丫' THEN 'Y'
            WHEN SUBSTRING(@str, @intLen, 1) >= N'夕' THEN 'X'
            WHEN SUBSTRING(@str, @intLen, 1) >= N'屲' THEN 'W'
            WHEN SUBSTRING(@str, @intLen, 1) >= N'他' THEN 'T'
            WHEN SUBSTRING(@str, @intLen, 1) >= N'仨' THEN 'S'
            WHEN SUBSTRING(@str, @intLen, 1) >= N'呥' THEN 'R'
            WHEN SUBSTRING(@str, @intLen, 1) >= N'七' THEN 'Q'
            WHEN SUBSTRING(@str, @intLen, 1) >= N'妑' THEN 'P'
            WHEN SUBSTRING(@str, @intLen, 1) >= N'噢' THEN 'O'
            WHEN SUBSTRING(@str, @intLen, 1) >= N'拏' THEN 'N'
            WHEN SUBSTRING(@str, @intLen, 1) >= N'嘸' THEN 'M'
            WHEN SUBSTRING(@str, @intLen, 1) >= N'垃' THEN 'L'
            WHEN SUBSTRING(@str, @intLen, 1) >= N'咔' THEN 'K'
            WHEN SUBSTRING(@str, @intLen, 1) >= N'丌' THEN 'J'
            WHEN SUBSTRING(@str, @intLen, 1) >= N'铪' THEN 'H'
            WHEN SUBSTRING(@str, @intLen, 1) >= N'旮' THEN 'G'
            WHEN SUBSTRING(@str, @intLen, 1) >= N'發' THEN 'F'
            WHEN SUBSTRING(@str, @intLen, 1) >= N'妸' THEN 'E'
            WHEN SUBSTRING(@str, @intLen, 1) >= N'咑' THEN 'D'
            WHEN SUBSTRING(@str, @intLen, 1) >= N'嚓' THEN 'C'
            WHEN SUBSTRING(@str, @intLen, 1) >= N'八' THEN 'B'
            WHEN SUBSTRING(@str, @intLen, 1) >= N'吖' THEN 'A'
            ELSE RTRIM(LTRIM(SUBSTRING(@str, @intLen, 1)))
        END;

        -- 非漢字（ASCII > 127 視為特殊符號）不產生拼音碼
        IF (ASCII(@temp) > 127) SET @temp = '';
        -- 英文括號不產生拼音碼
        IF @temp = '(' OR @temp = ')' SET @temp = '';

        SELECT @strRet = @temp + @strRet; -- 反向累加，最後再反轉或直接由後往前處理
        SET @intLen = @intLen - 1;
    END

    RETURN LOWER(@strRet);
END

測試範例

SELECT Product_ID, dbo.fn_GetPy(Product_Name) AS pymc
FROM dbo.T_Product;

原理與限制說明

• 採用「區間比對法」：依 Unicode 編碼範圍判斷首字母，效能優於查表法。
• 僅支援常用漢字範圍，生僻字、擴展區字元可能歸類錯誤或落入 ELSE 保留原字元。
• 英文、數字、符號會被過濾（ASCII > 127 或括號判斷），若需保留英文字母請調整 ELSE 邏輯。
• 函數為標量函數，大量資料逐列呼叫效能較差；建議改為計算欄位或觸發器預先計算存儲，查詢時直接使用索引。

效能優化建議

1. 計算欄位：ALTER TABLE T_Product ADD PinyinCode AS dbo.fn_GetPy(Product_Name) PERSISTED; 並建立索引。
2. 觸發器：INSERT/UPDATE 時寫入獨立欄位，避免查詢時即時運算。
3. CLR 函數：若極度追求效能，可用 C# 實作 CLR 標量函數，速度提升數倍。