# Alphanumeric Natural sort

• 06-25-2013, 03:26 PM
dowskee
Alphanumeric Natural sort
Hello everyone.

I'm new to Oracle and have been at this for a while. I know I'm close but my data is just not sorting correctly. Can any experts chime in? THANKS!

ORDER BY TO_NUMBER(REGEXP_SUBSTR(a, '[0-9]+')), a

Outputs:
2B
3A
3B
4B
4B
4A
4B
4A
5
6

Need:
2B
3A
3B
4A
4A
4B
4B
4B
5
6

I've also tried the following with no success:
ORDER BY TO_NUMBER(REGEXP_SUBSTR(a, '^[[:digit:]]*')), a
• 06-25-2013, 04:57 PM
gandolf989
What is the purpose of the following.
If you simply do an order by it works.

"ORDER BY TO_NUMBER(REGEXP_SUBSTR(a, '^[[:digit:]]*')), a"

Code:

```SYS@rdbadb01 AS SYSDBA> create table system.delme ( columna VARCHAR2(2) ); Table created. Elapsed: 00:00:00.31 SYS@rdbadb01 AS SYSDBA> SYS@rdbadb01 AS SYSDBA> SYS@rdbadb01 AS SYSDBA> INSERT INTO system.delme VALUES ('2B'); 1 row created. Elapsed: 00:00:00.01 SYS@rdbadb01 AS SYSDBA> INSERT INTO system.delme VALUES ('3A'); 1 row created. Elapsed: 00:00:00.00 SYS@rdbadb01 AS SYSDBA> INSERT INTO system.delme VALUES ('3B'); 1 row created. Elapsed: 00:00:00.00 SYS@rdbadb01 AS SYSDBA> INSERT INTO system.delme VALUES ('4B'); 1 row created. Elapsed: 00:00:00.01 SYS@rdbadb01 AS SYSDBA> INSERT INTO system.delme VALUES ('4B'); 1 row created. Elapsed: 00:00:00.00 SYS@rdbadb01 AS SYSDBA> INSERT INTO system.delme VALUES ('4A'); 1 row created. Elapsed: 00:00:00.00 SYS@rdbadb01 AS SYSDBA> INSERT INTO system.delme VALUES ('4B'); 1 row created. Elapsed: 00:00:00.00 SYS@rdbadb01 AS SYSDBA> INSERT INTO system.delme VALUES ('4A'); 1 row created. Elapsed: 00:00:00.01 SYS@rdbadb01 AS SYSDBA> INSERT INTO system.delme VALUES ('5'); 1 row created. Elapsed: 00:00:00.00 SYS@rdbadb01 AS SYSDBA> INSERT INTO system.delme VALUES ('6'); 1 row created. Elapsed: 00:00:00.00 SYS@rdbadb01 AS SYSDBA> SYS@rdbadb01 AS SYSDBA> COMMIT; Commit complete. Elapsed: 00:00:00.00 SYS@rdbadb01 AS SYSDBA> SYS@rdbadb01 AS SYSDBA> select * from system.delme order by 1; COLUMN ------ 2B 3A 3B 4A 4A 4B 4B 4B 5 6 10 rows selected. Elapsed: 00:00:00.10```
• 06-25-2013, 05:19 PM
dowskee
There are other conditions in the order by and this just happens to be the 4th condition down. The simple order by doesn't give me the correct results.

Using:
ORDER BY TO_NUMBER(REGEXP_SUBSTR(a, '[0-9]+')), a

Is the second character being sorted correctly?
• 06-25-2013, 05:57 PM
gandolf989
It would help to look at what the regexp was doing with the column.
You can't fix the code, if you don't know what it is doing.

Code:

```SYS@rdbadb01 AS SYSDBA> select TO_NUMBER(REGEXP_SUBSTR(COLUMNA, '[0-9]+')) regexp,   2  COLUMNA from system.delme order by 1;     REGEXP COLUMN ---------- ------         2 2B         3 3A         3 3B         4 4B         4 4B         4 4A         4 4A         4 4B         5 5         6 6 10 rows selected. Elapsed: 00:00:00.01```