In Oracle9i and later, sorting of Chinece text can follow phoneitc, radical, or stroke order instead of binary encoding. This behavior is controlled via the NLS_SORT setting.

Available linguistic sorts:

• SCHINESE_RADICAL_M: Sort by radical first, then stroke count.
• SCHINESE_STROKE_M: Sort by stroke count first, then radical.
• SCHINESE_PINYIN_M: Sort by Pinyin pronunciation.

Apply these orders using NLSSORT:

SELECT * FROM employee ORDER BY NLSSORT(name, 'NLS_SORT=SCHINESE_PINYIN_M');
SELECT * FROM employee ORDER BY NLSSORT(name, 'NLS_SORT=SCHINESE_STROKE_M');
SELECT * FROM employee ORDER BY NLSSORT(name, 'NLS_SORT=SCHINESE_RADICAL_M');

To set the sort mode for the current session:

ALTER SESSION SET NLS_SORT='SCHINESE_PINYIN_M';

Handling NULLs with NVL, NVL2, and NULLIF

NVL

Syntax: NVL(test_val, substitute_val)
Returns substitute_val if test_val is NULL; otherwise returns test_val. Both arguments must share compatilbe data types.

Examples:

SELECT NVL(NULL, 'unknown') AS gender FROM dual;
SELECT NVL('1', '2') AS gender FROM dual;

Practical usage—replace NULL in UseFlag with '0':

SELECT NVL(e.UseFlag, '0') AS usage_flag, e.name AS doc_name
FROM Dxc_Receive_Notice e;

NVL2

Syntax: NVL2(test_val, not_null_result, null_result)
Returns not_null_result if test_val is not NULL; otherwise returns null_result. If not_null_result and null_result differ in type, null_result is cast to match not_null_result.

Examples:

SELECT NVL2(0, 1, 2) FROM dual;
SELECT NVL2(NULL, 1, 2) FROM dual;

Practical usage—return UseFlag if present, else '0':

SELECT NVL2(e.UseFlag, e.UseFlag, '0') AS usage_flag, e.name AS doc_name
FROM Dxc_Receive_Notice e;

NULLIF

Syntax: NULLIF(val1, val2)
Returns NULL if val1 equals val2; otherwise returns val1.

Examples:

SELECT NULLIF(1, 1) FROM dual;
SELECT NULLIF(1, 2) FROM dual;

Replace a default date literal '0001/1/1' with NULL:

SELECT NULLIF(e.RECEIVEDATE, TO_DATE('0001/01/01','YYYY-MM-DD HH24:MI:SS')) AS recv_date,
       e.name AS doc_name
FROM Dxc_Receive_Notice e;

Conditional Translation Using DECODE

Syntax: DECODE(expr, match1, result1, match2, result2, ..., default)
Acts as a compact multi-way IF-THEN-ELSE. Returns resultN when expr matches matchN, or default if no match occurs.

Example mapping numeric sex codes to labels:

SELECT id, username, age, DECODE(sex, 0, 'Male', 1, 'Female') AS gender_lbl
FROM users;

The DECODE function can emulate comparisons by preprocessing expr with functions or calculations, enabling range checks despite its equality-based structure.