Oracle 函數實作：漢字轉拼音首字母 (支援模糊查詢)

核心原理

利用 NLSSORT(string, 'NLS_SORT=SCHINESE_PINYIN_M') 取得漢字拼音排序鍵值，透過已知音節邊界字元（如「澳」屬 a 結尾、「八」屬 b 開頭）建立對照區間，逐字比對轉換。

關鍵步驟

1. 將字串拆解為單字：SUBSTR(str, LEVEL, 1) 配合 CONNECT BY LEVEL <= LENGTH(str)。
2. 對每字計算 NLSSORT 排序值。
3. 與 26 個音節區間邊界值比較，對應 a~z。
4. 非漢字（英文、數字、符號）保持原樣或轉換為空。

完整函數程式碼

CREATE OR REPLACE FUNCTION f_trans_pinyin_capital(p_name IN VARCHAR2)
RETURN VARCHAR2 AS
  v_compare VARCHAR2(100);
  v_return  VARCHAR2(4000);

  FUNCTION f_nlssort(p_word IN VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
    RETURN NLSSORT(p_word, 'NLS_SORT=SCHINESE_PINYIN_M');
  END;
BEGIN
  FOR i IN 1..LENGTH(p_name) LOOP
    v_compare := f_nlssort(SUBSTR(p_name, i, 1));
    IF v_compare BETWEEN f_nlssort('吖') AND f_nlssort('啊') THEN v_return := v_return || 'a';
    ELSIF v_compare BETWEEN f_nlssort('八') AND f_nlssort('簿') THEN v_return := v_return || 'b';
    ELSIF v_compare BETWEEN f_nlssort('嚓') AND f_nlssort('咑') THEN v_return := v_return || 'c';
    ELSIF v_compare BETWEEN f_nlssort('哒') AND f_nlssort('咥') THEN v_return := v_return || 'd';
    ELSIF v_compare BETWEEN f_nlssort('妸') AND f_nlssort('誒') THEN v_return := v_return || 'e';
    ELSIF v_compare BETWEEN f_nlssort('发') AND f_nlssort('旮') THEN v_return := v_return || 'f';
    ELSIF v_compare BETWEEN f_nlssort('旮') AND f_nlssort('戈') THEN v_return := v_return || 'g';
    ELSIF v_compare BETWEEN f_nlssort('哈') AND f_nlssort('呵') THEN v_return := v_return || 'h';
    ELSIF v_compare BETWEEN f_nlssort('击') AND f_nlssort('佼') THEN v_return := v_return || 'j';
    ELSIF v_compare BETWEEN f_nlssort('咔') AND f_nlssort('垃') THEN v_return := v_return || 'k';
    ELSIF v_compare BETWEEN f_nlssort('垃') AND f_nlssort('啦') THEN v_return := v_return || 'l';
    ELSIF v_compare BETWEEN f_nlssort('妈') AND f_nlssort('么') THEN v_return := v_return || 'm';
    ELSIF v_compare BETWEEN f_nlssort('拿') AND f_nlssort('哪') THEN v_return := v_return || 'n';
    ELSIF v_compare BETWEEN f_nlssort('哦') AND f_nlssort('啪') THEN v_return := v_return || 'o';
    ELSIF v_compare BETWEEN f_nlssort('啪') AND f_nlssort('泼') THEN v_return := v_return || 'p';
    ELSIF v_compare BETWEEN f_nlssort('期') AND f_nlssort('齐') THEN v_return := v_return || 'q';
    ELSIF v_compare BETWEEN f_nlssort('然') AND f_nlssort('撒') THEN v_return := v_return || 'r';
    ELSIF v_compare BETWEEN f_nlssort('撒') AND f_nlssort('死') THEN v_return := v_return || 's';
    ELSIF v_compare BETWEEN f_nlssort('他') AND f_nlssort('踏') THEN v_return := v_return || 't';
    ELSIF v_compare BETWEEN f_nlssort('挖') AND f_nlssort('豆') THEN v_return := v_return || 'w';
    ELSIF v_compare BETWEEN f_nlssort('昔') AND f_nlssort('息') THEN v_return := v_return || 'x';
    ELSIF v_compare BETWEEN f_nlssort('呀') AND f_nlssort('咿') THEN v_return := v_return || 'y';
    ELSIF v_compare BETWEEN f_nlssort('匝') AND f_nlssort('咱') THEN v_return := v_return || 'z';
    ELSE v_return := v_return || SUBSTR(p_name, i, 1); -- 非漢字保留
    END IF;
  END LOOP;
  RETURN v_return;
END;

應用範例

SELECT f_trans_pinyin_capital('羅華') FROM dual;  -- 回傳 'lh'

-- 模糊查詢：使用者輸入中文或拼音首字母皆可查詢
SELECT * FROM users
WHERE name = :input
   OR f_trans_pinyin_capital(name) = :input;

效能優化建議

• 建立函數索引：CREATE INDEX idx_users_pinyin ON users (f_trans_pinyin_capital(name));
• 若資料量極大，考慮在應用層預計算存欄位，避免查詢時即時轉換。
• 注意 NLS_SORT 參數需與資料庫字元集相容（如 ZHS16GBK、AL32UTF8）。