Pinyin-related functions - PolarDB - Alibaba Cloud Documentation Center

PolarDB for PostgreSQL (Compatible with Oracle) provides two functions, fn_getpy and get_pyjm, to convert Chinese character strings into Pinyin. When you need to perform searches, sorting, or indexing based on the pronunciation of Chinese characters, you can use these functions to quickly obtain the full Pinyin or the first letter of each Pinyin syllable. This addresses the limitation of native Oracle syntax, which cannot directly retrieve the Pinyin of Chinese characters.

PolarDB includes two commonly used Pinyin-related functions to meet different business scenario needs:

fn_getpy: A comprehensive Pinyin conversion function that supports converting Chinese characters into various formats, such as full Pinyin (uppercase or lowercase), full Pinyin with capitalized initials, and Pinyin initials (uppercase or lowercase).
get_pyjm: A simplified shortcut function specifically designed for quickly obtaining the uppercase Pinyin initials of a Chinese character string.

Prerequisites

The supported version of PolarDB for PostgreSQL (Compatible with Oracle) is as follows:

Oracle syntax compatibility 2.0 (minor engine version 2.0.14.17.35.0 and later)

Note

The Pinyin-related functions are not enabled by default. If you need to use them, contact us to enable them.
You can view the minor engine version number in the console or by running the SHOW polardb_version; statement. If the minor engine version does not meet the requirements, upgrade the minor engine version.

Notes

When you use the Pinyin-related functions, note the differences between their behavior and the native Oracle behavior:

Polyphonic characters: The fn_getpy and get_pyjm functions in Oracle cannot process polyphonic characters. Unlike Oracle, which assumes a fixed pronunciation for each character, PolarDB can process common polyphonic characters to produce results that are more aligned with Chinese language conventions.
Example of polyphonic characters
```
postgres=# SELECT fn_getpy('重庆') from dual;
 fn_getpy
-----------
 chongqing

postgres=# SELECT fn_getpy('重要') from dual;
 fn_getpy
----------
 zhongyao

postgres=# SELECT get_pyjm('重庆') from dual;
 get_pyjm
----------
 CQ

postgres=# SELECT get_pyjm('重要') from dual;
 get_pyjm
----------
 ZY
```
Mixed character strings: When a string containing both Chinese and non-Chinese characters is passed as a parameter to the fn_getpy function, the native Oracle function converts the Chinese characters to Pinyin and retains the non-Chinese characters. In contrast, the PolarDB function ignores the non-Chinese characters and returns only the Pinyin of the Chinese characters. For example, for the string 'AChina123', the function returns 'zhongguo'.
NULL value handling: If a NULL parameter is passed to the get_pyjm function, the native Oracle function returns an exception. In contrast, the PolarDB function returns NULL.

fn_getpy function

The fn_getpy function converts a Chinese character string into the corresponding Pinyin based on the output format that you specify.

Function

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

Parameters

Parameter

Description

p_str

The input Chinese character string.

p_flag

The output Pinyin format. Valid values:

0 (default): Full Pinyin in lowercase.
1: Full Pinyin in uppercase.
2: Full Pinyin with the first letter capitalized.
3: First letter only, in lowercase.
4: First letter only, in uppercase.

Note

If this parameter is not specified (NULL) or is set to a value outside the valid range, the default value 0 is used.

Example

-- Default format (full Pinyin in lowercase)
SELECT fn_getpy('拼音') FROM dual;
-- Returns: pinyin

-- Full Pinyin in uppercase
SELECT fn_getpy('拼音', 1) FROM dual;
-- Returns: PINYIN

-- Full Pinyin, with the first letter capitalized
SELECT fn_getpy('拼音', 2) FROM dual;
-- Returns: PinYin

-- First letter only, in lowercase
SELECT fn_getpy('拼音', 3) FROM dual;
-- Returns: py

-- First letter only, in uppercase
SELECT fn_getpy('拼音', 4) FROM dual;
-- Returns: PY

get_pyjm function

The get_pyjm function is used to quickly retrieve the first letter of the Pinyin of a string in uppercase. This is equivalent to fn_getpy(p_str, 4).

Function

FUNCTION sys.get_pyjm(
    P_NAME IN VARCHAR2
)
RETURN VARCHAR2

Example

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