Home » SQL & PL/SQL » SQL & PL/SQL » SQL to sort a column containing numeric/characters/slphanumeric characters
SQL to sort a column containing numeric/characters/slphanumeric characters [message #389840] Tue, 03 March 2009 14:35 Go to next message
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 Sad. 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;
Re: SQL to sort a column containing numeric/characters/alphanumeric characters [message #389842 is a reply to message #389840] Tue, 03 March 2009 14:37 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

This is a FAQ, so SEARCH, SEARCH, SEARCH before posting!
Re: SQL to sort a column containing numeric/characters/slphanumeric characters [message #389848 is a reply to message #389840] Tue, 03 March 2009 15:13 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
vinaynp wrote on Tue, 03 March 2009 15:35

AFTER FIX
SQL> <MAGIC SQL statement>



I like that Smile

Have a look at an ORDER BY clause with two components. First, sort by the number portion of the column and second by the character string portion.

To find these atomic parts, you might want to use the REPLACE or TRANSLATE functions. If you search here, you will find what you are looking for.
Re: SQL to sort a column containing numeric/characters/slphanumeric characters [message #389877 is a reply to message #389848] Tue, 03 March 2009 21:14 Go to previous message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
http://www.orafaq.com/forum/m/389389/43710/#msg_389389
Previous Topic: question based on Oracle PL/SQL Programming book code
Next Topic: Cursor For Loop
Goto Forum:
  


Current Time: Sat Dec 03 20:20:26 CST 2016

Total time taken to generate the page: 0.16824 seconds