Sorting a query in ASCII code
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Sorting a query in ASCII code

Hybrid View

  1. #1
    Join Date
    May 2002
    Posts
    6
    Hi,
    I've got a problem when I sort the results. Look this.
    The original set was:

    cc
    aa
    bb
    11
    33
    22

    And i want to sort this with the ascending ascii criteria:

    11
    22
    33
    aa
    bb
    cc

    But Oracle return, doing an 'order by' this set:

    aa
    bb
    cc
    11
    22
    33

    How can I solve this problem? Is there any way to
    specify the ascii criteria in the 'order by' clause?
    That is, i want something like:

    'order by ascii(column)'

    but not for only the first character; i want this for all
    the characters of the field

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    U can use DUMP for sort with the ascending ascii criteria:
    ...
    order by substr(dump(field_name), insrt(dump(field_name),':') + 2) asc


    [Edited by Shestakov on 05-09-2002 at 03:51 PM]

  3. #3
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    SQL> select table_name, substr(dump(table_name), instr(dump(table_name),':') + 2) from all_tables
    where rownum < 10;
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    IND$ 73,78,68,36
    FILE$ 70,73,76,69,36
    UNDO$ 85,78,68,79,36
    CLU$ 67,76,85,36
    BOOTSTRAP$ 66,79,79,84,83,84,82,65,80,36
    ICOL$ 73,67,79,76,36

  4. #4
    Join Date
    May 2002
    Posts
    6
    It doesn't work. The sorting must be applied to all the
    column, not only for the 2 first characters

  5. #5
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Originally posted by id
    It doesn't work. The sorting must be applied to all the
    column, not only for the 2 first characters
    It works.

    SQL> create table train.test_tbl (a varchar2(10), b varchar2(10));
    insert into train.test_tbl values('aaa', 'aa111');
    insert into train.test_tbl values('bbb', 'aa222');
    insert into train.test_tbl values('ccc', 'aa333');
    insert into train.test_tbl values('11a', 'aa444');
    insert into train.test_tbl values('21a', 'aa4aa');
    insert into train.test_tbl values('1a2', 'aa4bb');
    commit;

    select a, substr(dump(a), instr(dump(a),':') + 2) c from train.test_tbl
    order by substr(dump(a), instr(dump(a),':') + 2);
    A C
    -------------------- ------------------------------
    11a 49,49,97
    1a2 49,97,50
    21a 50,49,97
    aaa 97,97,97
    bbb 98,98,98
    ccc 99,99,99

    6 rows selected.

    SQL>
    select b, substr(dump(b), instr(dump(b),':') + 2) c from train.test_tbl
    order by substr(dump(b), instr(dump(b),':') + 2);

    B C
    -------------------- ------------------------------
    aa111 97,97,49,49,49
    aa222 97,97,50,50,50
    aa333 97,97,51,51,51
    aa444 97,97,52,52,52
    aa4aa 97,97,52,97,97
    aa4bb 97,97,52,98,98

    6 rows selected.

  6. #6
    Join Date
    May 2002
    Posts
    6

    Mmm

    Well, it works only if the characters set is into 0 to 99.
    It doesn't work with chars greater than 99. Try to sort this:

    raul
    ana
    bis

    The result must be:

    ana
    bis
    raul

    But the result is:

    raul
    ana
    bis

    'cause raul starts with a character greater than 99

  7. #7
    Join Date
    May 2002
    Posts
    108

    Angry :( whats the problem?

    Hi,

    Honestly, I don't understand the problem.

    When I run the query I get the sorted output as 'u' expect
    i.,e

    11
    22
    33
    aa
    bb

    etc.,

    Can u post which version of Oracle u r using? I am using 8.1.7

    I tried the same with the data 'raul, ana, bis' as well... and getting it right as u expect.

    What is that u r trying to do?

    Do correct me if I am wrong!

    Cheers
    Nandu


    Never give up !

    Nanda Kumar - Vellore

  8. #8
    Join Date
    May 2002
    Posts
    6
    Hi,
    Maybe the problem is my poor english :-/
    Well, I'll try to explain this with a practical example.
    I've got this list of unsorted items:

    select a from test_tbl
    11a
    ana
    1a2
    raul
    bis
    21a
    aaa
    bbb
    ccc

    And when I sort that set, I get the next result:

    select a from test_tbl order by a
    aaa
    ana
    bbb
    bis
    ccc
    raul
    1a2
    11a
    21a

    Obviusly, this result is not sorted in ascii criteria ('cause
    '1a2' code is less than 'aaa'). I don't know how I can sort
    in the ascending ascii order criteria. The result that I expect
    would be:

    11a
    1a2
    21a
    aaa
    ana
    bbb
    bis
    ccc
    raul

    And with the solution that you propose to me I get this result:

    select a from test_tbl order by substr(dump(a), instr(dump(a),':') + 2);
    raul
    11a
    1a2
    21a
    ana
    aaa
    bis
    bbb
    ccc

    Ooops, 'raul' is in the first position! This occurs 'cause 'raul'
    starts with a character greater than 100, returned by the
    dump instruction. And then, 'raul' -> '100,xx,xx' is smaller than '11a' -> '50,xx,xx'
    Hope that we can understand the problem


  9. #9
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    U just have to read little bit more about DUMP()

    SQL> insert into train.test_tbl values ('rrrr','11r11');

    1 row created.

    SQL> insert into train.test_tbl values ('123rr', 'r1r1r1');

    1 row created.

    select b, substr(dump(b,16), instr(dump(b,16),':') + 2) c from train.test_tbl
    order by substr(dump(b,16), instr(dump(b,16),':') + 2);
    B C
    ------------------------------ --------------------
    11r11 31,31,72,31,31
    aa111 61,61,31,31,31
    aa222 61,61,32,32,32
    aa333 61,61,33,33,33
    aa444 61,61,34,34,34
    aa4aa 61,61,34,61,61
    aa4bb 61,61,34,62,62
    r1r1r1 72,31,72,31,72,31

    select a, substr(dump(a,16), instr(dump(a,16),':') + 2) c from train.test_tbl
    order by substr(dump(a,16), instr(dump(a,16),':') + 2);

    A C
    -------------------- --------------------
    11a 31,31,61
    123rr 31,32,33,72,72
    1a2 31,61,32
    21a 32,31,61
    aaa 61,61,61
    bbb 62,62,62
    ccc 63,63,63
    rrrr 72,72,72,72


    I hope it may be help u.

  10. #10
    Join Date
    May 2002
    Posts
    6
    I tried to do this, but it doen't work.
    When we pass '16' in the dump instruction, it
    returns the hexadecimal code representation.
    But we're in the same problem when we compare
    codes with alpha digits. That is:

    'a' -> 61 //Hexa
    'o' -> 6f //Hexa

    When i try to sort these 2 strings i get the same
    incorrect result that i described:

    'o'
    'a'

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