| SQL to sort a column containing numeric/characters/slphanumeric characters [message #389840] |
Tue, 03 March 2009 14:35  |
vinaynp
Messages: 1
Registered: March 2009
|
Junior Member |
|
|
I 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;
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
