Alphanumeric sorting in Oracle

Question

I am selecting the column names from a 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 resultset is like

INFORMATION1
INFORMATION10
INFORMATION11
INFORMATION12
.
.
.
INFORMATION2
INFORMATION20

Now, I want it to be sorted like

INFORMATION1
INFORMATION2
INFORMATION3
INFORMATION4
.
.
.
INFORMATION19
INFORMATION20

How to achieve this without being much cpu-intense?? Thanks in advance!

Answer 1

Order first by string length, then by 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 much cpu-intense?

REGEX is CPU intensive and slower as compared to old SUBSTR. Use SUBSTR to get the digit part and use it in the ORDER BY.

Also, since you are filtering rows only with INFORMATION string, you need only one ORDER BY on the digit part.

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));

You can hard-code the LENGTH as 12.

ORDER BY TO_NUMBER(SUBSTR(column_name, 12))

Here is the SQL Fiddle demo.