-
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
-
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
-
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?
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|