SQL to sort a column containing numeric/characters/alphanumeric characters.
688849Mar 3 2009 — edited Mar 3 2009I have a table whose column can contain numbers, characters, and a combination of numbers and characters. Below is an example of the table. In this regard, I would like to sort the table in the following way.
a) Sort first the numbers in numeric order and then would sort the remaining in character order.
Example :-
CURRENT SQL
SQL> select * from temp;
ID
---------------
A
22
46
CC
BBB
46jkb
1
10
100
a
AFTER FIX
SQL> <MAGIC SQL statement>
ID
---------------
1
10
22
46
46jkb
100
a
A
BBB
CC
I have tried asciistr, to_char, and to_number and many other combinations. However, no luck :-(. Any help will be highly appreciated.
---SQL statement to create and insert data in table--
create table temp( id varchar2(15));
insert into temp values ('A');
insert into temp values ('22');
insert into temp values ('46');
insert into temp values ('CC');
insert into temp values ('BBB');
insert into temp values ('46jkb');
insert into temp values ('1');
insert into temp values ('10');
insert into temp values ('100');
insert into temp values ('a');
commit;