Home » SQL & PL/SQL » SQL & PL/SQL » order by alphanumeric (10g, vista)
order by alphanumeric [message #562408] Wed, 01 August 2012 06:53 Go to next message
rzkhan
Messages: 320
Registered: March 2005
Senior Member


create table x(
sno varchar2(5)
);


insert into x values('A-1');
insert into x values('B-1');
insert into x values('B-2');
insert into x values('B-3');
insert into x values('1');
insert into x values('A-2');
insert into x values('2');
insert into x values('3');
insert into x values('A-4');
insert into x values('B-4');
insert into x values('C-4');
insert into x values('D-4');

 
SQL>select * from x;

SNO
-----
A-1
B-1
1
A-2
2
3
A-4
B-4
C-4
D-4
B-2
B-3


How can I select it ike this

1
A-1
A-2
A-4
B-1
2
B-2
3
B-3
B-4
C-4
D-4


Re: order by alphanumeric [message #562409 is a reply to message #562408] Wed, 01 August 2012 07:04 Go to previous messageGo to next message
Littlefoot
Messages: 19303
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What rule has to be applied in order to get such a sort?
Re: order by alphanumeric [message #562411 is a reply to message #562408] Wed, 01 August 2012 07:04 Go to previous messageGo to next message
cookiemonster
Messages: 10846
Registered: September 2008
Location: Rainy Manchester
Senior Member
I don't understand the rule you are trying to apply.
Why do 2 and 3 appear in the positions they do? Why don't they come between the A values?
Explain your rule in words.
Re: order by alphanumeric [message #562412 is a reply to message #562408] Wed, 01 August 2012 07:06 Go to previous messageGo to next message
Michel Cadot
Messages: 58501
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What are the rules?

Regards
Michel
Re: order by alphanumeric [message #562423 is a reply to message #562412] Wed, 01 August 2012 07:41 Go to previous messageGo to next message
rzkhan
Messages: 320
Registered: March 2005
Senior Member
First of all I am so sorry I could not post the required well in required order.


The rule is:
If a number is without Alphabet it should come first

like

1
A-1
A-2
A-4
2
B-2
3
B-3
B-4
C-4
D-4


Re: order by alphanumeric [message #562426 is a reply to message #562423] Wed, 01 August 2012 07:46 Go to previous messageGo to next message
Michel Cadot
Messages: 58501
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
If a number is without Alphabet it should come first


Why 2 is NOT before A-2?
And 3 not before A-4?

Regards
Michel
Re: order by alphanumeric [message #562431 is a reply to message #562426] Wed, 01 August 2012 07:53 Go to previous messageGo to next message
Michel Cadot
Messages: 58501
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And also, where disappear B-1? Where should it be? Before or after 2?

Regards
Michel

[Updated on: Wed, 01 August 2012 08:00]

Report message to a moderator

Re: order by alphanumeric [message #562442 is a reply to message #562423] Wed, 01 August 2012 08:01 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1949
Registered: January 2010
Senior Member
rzkhan wrote on Wed, 01 August 2012 08:41
The rule is:
If a number is without Alphabet it should come first


If that would be the rule results would be:

select  *
  from  x
  order by to_number(regexp_substr(sno,'\d+')),
           regexp_replace(sno,'\d') nulls first
/

SNO
-----
1
A-1
B-1
2
A-2
B-2
3
B-3
A-4
B-4
C-4

SNO
-----
D-4

12 rows selected.

SQL> 


SY.
Re: order by alphanumeric [message #562443 is a reply to message #562442] Wed, 01 August 2012 08:12 Go to previous messageGo to next message
John Watson
Messages: 4370
Registered: January 2010
Location: Global Village
Senior Member
I don't unserstand the "rule", but does this help -
orcl> select * from x order by reverse(sno);

SNO
-----
1
A-1
B-1
2
A-2
B-2
3
B-3
A-4
B-4
C-4
D-4

12 rows selected.

orcl>

Re: order by alphanumeric [message #562480 is a reply to message #562443] Wed, 01 August 2012 11:03 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1949
Registered: January 2010
Senior Member
REVERSE is not documeneted. REVERSE reverses bytes, not characters and in case of muli-byte character set result will be not what you'd expect. But in this particular task, sorting by REVERSE(SNO) doesn't work at all. We will face same issue. String '10' is less than string '2' while number 10 is greater than number 2. So If we change data sample to 10 instead of 1:

SQL> select * from x;

SNO
-----
A-10
B-10
B-2
B-3
10
A-2
2
3
A-4
B-4
C-4

SNO
-----
D-4

12 rows selected.

SQL> select * from x order by reverse(sno);

SNO
-----
10
A-10
B-10
2
A-2
B-2
3
B-3
A-4
B-4
C-4

SNO
-----
D-4

12 rows selected.

SQL> select  *
  2    from  x
  3    order by to_number(regexp_substr(sno,'\d+')),
  4             regexp_replace(sno,'\d') nulls first
  5  /

SNO
-----
2
A-2
B-2
3
B-3
A-4
B-4
C-4
D-4
10
A-10

SNO
-----
B-10

12 rows selected.

SQL> 


SY.

[Updated on: Wed, 01 August 2012 11:03]

Report message to a moderator

Re: order by alphanumeric [message #562482 is a reply to message #562480] Wed, 01 August 2012 11:17 Go to previous messageGo to next message
Michel Cadot
Messages: 58501
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Advice: use
set pages 10000

Regards
Michel
Re: order by alphanumeric [message #562522 is a reply to message #562482] Wed, 01 August 2012 23:35 Go to previous messageGo to next message
rzkhan
Messages: 320
Registered: March 2005
Senior Member
Thank you all and I am Sorry , My English is not that good. I apologize for that..
I was thinking my example would be sufficient to understand my logic. 

However, I made some changes while assigning sno. 
I carried the number before alphabet while inserting into the table and this solved my problem :p 

insert into x values('1');
insert into x values('2');
insert into x values('1-A');
insert into x values('1-B');
insert into x values('2-A');
insert into x values('2-B');
insert into x values('3-A');
insert into x values('3-B');
insert into x values('4-A');
insert into x values('4-B');
insert into x values('5-A');
insert into x values('5-B');
insert into x values('6-A');
insert into x values('6-B');

Now I can easily select them in ascending order.

SQL> select * from x order by sno;

SNO
-----
1
1-A
1-B
2
2-A
2-B
3-A
3-B
4-A
4-B
5-A
5-B
6-A
6-B

Really Thanks to all of you guys...




Re: order by alphanumeric [message #562527 is a reply to message #562522] Thu, 02 August 2012 00:46 Go to previous messageGo to next message
Littlefoot
Messages: 19303
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Do the following:
insert into x values ('10-A');
and execute your SELECT statement again. Are you still happy?
Re: order by alphanumeric [message #562533 is a reply to message #562527] Thu, 02 August 2012 00:57 Go to previous messageGo to next message
rzkhan
Messages: 320
Registered: March 2005
Senior Member
Of course not happy Sad

DELETE FROM X;

insert into x values('1');
insert into x values('1-A');
insert into x values('1-B');
insert into x values('2');
insert into x values('2-A');
insert into x values('2-B');
insert into x values('3-A');
insert into x values('3-B');
insert into x values('4-A');
insert into x values('4-B');
insert into x values('5-A');
insert into x values('5-B');
insert into x values('6-A');
insert into x values('6-B');
insert into x values('10-A');
insert into x values('10-B');
insert into x values('11-A');
insert into x values('11-B');
insert into x values('17-A');
insert into x values('17-B');
insert into x values('22-A');
insert into x values('22-B');
insert into x values('98-A');
insert into x values('98-B');
insert into x values('100-A');
insert into x values('100-B');
insert into x values('112-A');
insert into x values('112-B');


SQL> ;
  1* select * from x order by lpad(sno,5)
SQL> /

SNO
-----
1-A
1-B
2-A
2-B
3-A
3-B
4-A
4-B
5-A
5-B
6-A
6-B
10-A
10-B
11-A
11-B
17-A
17-B
22-A
22-B
98-A
98-B
100-A
100-A
100-B
100-B
112-A
112-B


Admiror of your knowledge

Blessing...


Re: order by alphanumeric [message #562536 is a reply to message #562533] Thu, 02 August 2012 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 58501
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you don't want to care about value length:
SQL> select * from x order by to_number(regexp_substr(sno,'^\d*')), sno;
SNO
-----
1
1-A
1-B
2
2-A
2-B
3-A
3-B
4-A
4-B
5-A
5-B
6-A
6-B
10-A
10-B
11-A
11-B
17-A
17-B
22-A
22-B
98-A
98-B
100-A
100-B
112-A
112-B

Regards
Michel
Re: order by alphanumeric [message #562537 is a reply to message #562536] Thu, 02 August 2012 01:21 Go to previous message
rzkhan
Messages: 320
Registered: March 2005
Senior Member
a great forum with great teachers !!!!!
Previous Topic: encrypting password field
Next Topic: ORA-24247 with valid ACL when sending smtp mail
Goto Forum:
  


Current Time: Wed Jul 23 11:31:53 CDT 2014

Total time taken to generate the page: 0.13575 seconds