Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Posts
    2

    Unanswered: Order by with Alphanumeric values

    I need use an order by clause that will sort on alphanumeric values in single Varchar2 column. The sort needs to use the numeric value first, then next numeric value (in case of second or third dot) and then the alpha.
    MS Access has a "value" function that makes this fairly easy, but I have been unable to find the same thing in Oracle SQL.
    Any ideas would be greatly appreciated.

    Robert

    Sample data in column:

    5.0
    5.20
    5.19
    VeryOldValue
    5.19a
    10.1
    7.5
    10.8
    10.25a
    NewValue
    10.25b
    9.5.5
    9.5.5.1
    1.1.1
    1.1.2
    OldValue
    7.5.1

    It needs to sort into this order:

    1.1.1
    1.1.2
    5.0
    5.19
    5.19a
    5.20
    7.5
    7.5.1
    9.5.5
    9.5.5.1
    10.1
    10.8
    10.25a
    10.25b
    NewThing
    OldValue
    Verygood

  2. #2
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    ok, I assume you mean

    1.1.1
    1.1.2
    5.0
    5.19
    5.19a
    5.20
    7.5
    7.5.1
    9.5.5
    9.5.5.1
    10.1
    10.25a
    10.25b
    10.8
    NewThing
    OldValue
    Verygood

    ie the 10.25.a before 10.8.

    I have broken it down into a number part and then a text part and then ordered by the two fields, its a little complicated but I can't think of another way

    SELECT a,
    to_number(translate((substr(a,1,instr(a,'.')) || replace(substr(a,instr(a,'.')+1),'.','')),'1qwerty uioplkjhgfdsazxcvbnmQWERTYUIOPLKJHGFDSAZXCVBNM','1 ')) NUM_PART,
    translate(a,'a0123456789.','a') ALPHA_PART
    FROM test
    ORDER BY 2,3

    hth
    Robert

  3. #3
    Join Date
    Apr 2004
    Posts
    2
    Thank you for your interest. You are correct in the sort should have the 10.25(a,b) before the 10.8.

    I created a table test and inserted the values from my post into it to run your solution. Unfortunately this is the result:

    1 SELECT a,
    2 to_number(translate((substr(a,1,instr(a,'.')) ||
    3 replace(substr(a,instr(a,'.')+1),'. ','')),
    4 '1qwertyuioplkjhgfdsazxcvbnmQWERTYUIOPLKJHGFDSAZXC VBNM','1'))
    5 NUM_PART,
    6 translate(a,'a0123456789.','a') ALPHA_PART
    7 FROM test
    8* ORDER BY 2,3
    SQL> /
    ERROR:
    ORA-01722: invalid number

    no rows selected

    You did point me the right direction by spliting the numbers into 2 parts, before the first dot and after the first dot. What I ended up doing was stripping out the dots after the first one, stripping out any alpha or spaces, then sorting on the first part (num1) and then the second part (num2), then on column itself to put the alphas in order.
    Seems to work so far:

    select a,
    to_number(substr(a,1,instr(a,'.'))) num1,
    To_number(translate(substr(a,instr(a,'.')+1),'1234 567890. qwertyuioplkjhgfdsazxcvbnmQWERTYUIOPLKJHG
    FDSAZXCVBNM','1234567890')) num2
    from test
    order by 2,3,1


    Thanks,
    Robert G

Posting Permissions

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