Sorting of Chinese and digital hybrid fields in Oracle

How to sort fields in Oracle in the form of Chinese characters and numbers:

For example:

1. Order ByNLSSORT (field name,'Nls _ SORT = SCHINESE_PINYIN_M'), To_number (translate (field name,'123'| Field name,'123')) 

NLSSORT () function:

If the database character set is ZH16GBK, order by is sorted by the "binary encoding" order of Chinese characters by default.

You can use the NLSSORT () function to change Oralce's sorting rules for Chinese characters. The sorting rules are as follows (the first three are for simplified Chinese, and the last two are for traditional Chinese ):

1. SCHINESE_RADICAL_M

In simplified Chinese, the order is "radical" and the order is "number of strokes.

2. SCHINESE_STROKE_M

Simplified Chinese is sorted by the number of strokes in the first order, and by the "beginning" in the second order.

3. SCHINESE_PINYIN_M

Simplified Chinese is sorted by pinyin. In the preceding example, the simplified Chinese characters are sorted by pinyin.

4. TCHINESE_RADICAL_M

Traditional Chinese is sorted by the number of strokes in the first order and by the second order.

5. TCHINESE_STROKE_M

Traditional Chinese is sorted by the number of strokes in the first order and by the "beginning" in the second order.

TRANSLATE () function:

Syntax: TRANSLATE (char, from,)

Usage: replace each character in char from with the corresponding character in to. If from is longer than to, the extra characters in from are deleted. One of the three parameters is null, And the return value is also null.

This function can be used to remove Chinese characters from a field. For example:

1. SelectTRANSLATE ('Example 1213','123'|'Example 1213','123')AsRESULTFromDual;
2. RESULT
3. ------------
4. 1213

Analysis: This statement replaces the characters that appear in char -- 'example 1213 'in from -- 'example 0123456789' with those in to -- '20140901, based on the correspondence between from and to, replace 0 in char with 0, 1 with 1 ,... 9 is replaced with 9, that is, the number in the char remains unchanged, and the from ratio is longer than to, the extra characters in the from ratio are deleted, that is, the Chinese characters in the char are deleted.