All Products
Search

This Product

    PolarDB:Pinyin-related functions

    Document Center

    PolarDB:Pinyin-related functions

    Last Updated:Mar 28, 2026

    PolarDB for PostgreSQL (Compatible with Oracle) provides two functions, fn_getpy and get_pyjm, to convert Chinese character strings into Pinyin. Use these functions to search, sort, or index data by the pronunciation of Chinese characters.

    FunctionDescriptionEquivalent
    fn_getpyConverts a Chinese string to Pinyin in one of five formats—
    get_pyjmReturns uppercase Pinyin initials for a Chinese stringfn_getpy(p_str, 4)

    Choose fn_getpy when you need a specific output format. Use get_pyjm as a shortcut when uppercase initials are all you need.

    Prerequisites

    Before you begin, ensure that you have:

    • PolarDB for PostgreSQL (Compatible with Oracle), Oracle syntax compatibility 2.0 (minor engine version 2.0.14.17.35.0 or later)

    Pinyin-related functions are not enabled by default. Contact us to enable them.

    To check your minor engine version, run SHOW polardb_version; or view it in the console. If the version does not meet the requirement, upgrade the minor engine version.

    Usage notes

    PolarDB handles certain inputs differently from native Oracle. Be aware of the following differences before migrating Oracle workloads.

    Polyphonic characters

    PolarDB correctly handles common polyphonic characters (characters with multiple pronunciations), whereas Oracle assumes a fixed pronunciation for each character.

    SELECT fn_getpy('重庆') from dual;   -- Returns: chongqing
SELECT fn_getpy('重要') from dual;   -- Returns: zhongyao

SELECT get_pyjm('重庆') from dual;   -- Returns: CQ
SELECT get_pyjm('重要') from dual;   -- Returns: ZY

    Mixed character strings (fn_getpy only)

    When the input contains both Chinese and non-Chinese characters, PolarDB returns only the Pinyin of the Chinese characters. Oracle retains the non-Chinese characters in the output.

    Example: fn_getpy('AChina123') returns zhongguo.

    NULL handling (get_pyjm only)

    get_pyjm returns NULL when given a NULL input. Oracle raises an exception in the same situation.

    fn_getpy function

    Converts a Chinese string to Pinyin in the format specified by p_flag.

    Syntax

    FUNCTION sys.fn_getpy(
    p_str  IN VARCHAR2,
    p_flag    NUMBER DEFAULT NULL
) RETURN VARCHAR2

    Parameters

    ParameterDescription
    p_strThe input Chinese character string.
    p_flagThe output format. If omitted, set to NULL, or set to a value outside the valid range, defaults to 0.

    p_flag values

    p_flagFormatExample callReturns
    0 (default)Full Pinyin, lowercasefn_getpy('拼音')pinyin
    1Full Pinyin, uppercasefn_getpy('拼音', 1)PINYIN
    2Full Pinyin, first letter of each syllable capitalizedfn_getpy('拼音', 2)PinYin
    3Initials only, lowercasefn_getpy('拼音', 3)py
    4Initials only, uppercasefn_getpy('拼音', 4)PY

    All examples use FROM dual to match Oracle-compatible syntax:

    SELECT fn_getpy('拼音') FROM dual;      -- pinyin
SELECT fn_getpy('拼音', 1) FROM dual;   -- PINYIN
SELECT fn_getpy('拼音', 2) FROM dual;   -- PinYin
SELECT fn_getpy('拼音', 3) FROM dual;   -- py
SELECT fn_getpy('拼音', 4) FROM dual;   -- PY

    get_pyjm function

    Returns the uppercase Pinyin initials of a Chinese string. Equivalent to fn_getpy(p_str, 4).

    Syntax

    FUNCTION sys.get_pyjm(
    P_NAME IN VARCHAR2
)
RETURN VARCHAR2

    Example

    SELECT get_pyjm('拼音') FROM dual;   -- Returns: PY

    Related topics