Alphanumeric sorting in Oracle

Question

I select the column names from the table with the following query.

SELECT column_name FROM   all_tab_cols
WHERE  table_name = 'TBL1' AND column_name like 'INFORMATION%'
order by column_name

The result set is similar.

INFORMATION1
INFORMATION10
INFORMATION11
INFORMATION12
.
.
.
INFORMATION2
INFORMATION20

Now I want it to be sorted as

INFORMATION1
INFORMATION2
INFORMATION3
INFORMATION4
.
.
.
INFORMATION19
INFORMATION20

How to achieve this without having a large amount of processor? Thanks in advance!

Answer 1

First order the length of the string, then the string:

SELECT column_name 
FROM all_tab_cols
WHERE table_name = 'TBL1' 
  AND column_name LIKE 'INFORMATION%'
ORDER BY LENGTH(column_name), column_name;

SqlFiddleDemo

Answer 2

How to achieve this without being intense cpu-intensive?

REGEX is an intensive and slow CPU compared to the old SUBSTR . Use SUBSTR to get the digital part and use it in ORDER BY .

, INFORMATION, ORDER BY .

SELECT column_name FROM   all_tab_cols
WHERE  table_name = 'TBL1' AND column_name like 'INFORMATION%'
ORDER BY TO_NUMBER(SUBSTR(column_name, LENGTH('INFORMATION') +1));

LENGTH 12.

ORDER BY TO_NUMBER(SUBSTR(column_name, 12))

SQL Fiddle.

Answer 3

Change ORDER BY to

ORDER BY TO_NUMBER(SUBSTR(COLUMN_NAME, 12))

Good luck.

Answer 4

Try using this:

SELECT column_name FROM   all_tab_cols
WHERE  table_name = 'TBL1' AND column_name like 'INFORMATION%'
ORDER  BY regexp_substr(column_name,'[[:alpha:]]'),
to_number(regexp_substr(column_name, '\d+'))

FIDDLE DEMO