Sorting a query in ASCII code

Show 40 post(s) from this thread on one page
Page 1 of 2 12 Last
• 05-09-2002, 02:36 PM
id
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
• 05-09-2002, 03:46 PM
Shestakov
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]
• 05-09-2002, 03:54 PM
Shestakov
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
• 05-10-2002, 01:03 PM
id
It doesn't work. The sorting must be applied to all the
column, not only for the 2 first characters
• 05-10-2002, 01:48 PM
Shestakov
Quote:

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.
• 05-13-2002, 07:57 AM
id
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
• 05-13-2002, 12:08 PM
nandu
:( 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

• 05-13-2002, 02:11 PM
id
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

• 05-13-2002, 02:45 PM
Shestakov
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.
• 05-15-2002, 03:35 AM
id
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'
Show 40 post(s) from this thread on one page
Page 1 of 2 12 Last