Collation of numbers and Chinese characters in oracle and introduction to REGEXP_SUBSTR, oracleregexpsubstr

Collation of numbers and Chinese characters in oracle and introduction to REGEXP_SUBSTR, oracleregexpsubstr

Requirement: There is a column NAME, varchar2 type, the content is as follows

The preceding statements are sorted by order by name, but they are not the expected results.

Now you only need to sort by number

Step 1: extract numbers
It is difficult to use substr to intercept a number that is one digit and two digits.

We can useREGEXP_SUBSTRFunction that uses regular expressions to extract substrings from strings.

REGEXP_SUBSTR (name, '[0-9] + ')

Step 2: convert a number of the varchar type to a number of the int type

Cast (yourcol as int)

For details, refer to: Convert the varchar type to the int type in SQL and then sort it.

So combine

Select * from test order by cast (REGEXP_SUBSTR (name, '[0-9] +') as int );

You can sort by number.

Author: itmyhome