Printable View
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