# Thread: Alphanumeric Natural sort

1. Junior Member
Join Date
Jun 2013
Posts
2

## 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

2. 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```

3. Junior Member
Join Date
Jun 2013
Posts
2
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?

4. 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```

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•

Click Here to Expand Forum to Full Width

×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.