DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Alphanumeric Natural sort

  1. #1
    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. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    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. #3
    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. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    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