Home » SQL & PL/SQL » SQL & PL/SQL » Sorting strings in Order (Oracle 10g, release 10.2.0.1, WinXP)
Sorting strings in Order [message #437249] Sat, 02 January 2010 12:55 Go to next message
anil_mk
Messages: 137
Registered: August 2006
Location: Bangalore, India
Senior Member

Dear All,

Please let us know, how to sort strings in order.
Please check the below SQL statement with values of the column(strings) which needs to be sorted in order

select a
from (
select 'ITEM 18' a from dual union
select 'ITEM 10' a from dual union
select '18' a from dual union
select 'ITEM 1' a from dual union
select 'ITEM 11' a from dual union
select 'ITEM 12' a from dual union
select 'ITEM 7' a from dual union
select 'ITEM 8' a from dual union
select 'ITEM 3' a from dual union
select 'ITEM 300' a from dual union
select '1' a from dual union
select 'ITEM 300000' a from dual union
select 'ITEM 1' a from dual union
select 'ITEM GOLD' a from dual union
select 'ITEM 2' a from dual )
order by a
-----------------------------------------------------------------
RESULT AS SHOWN BELOW
-----------------------------------------------------------------
1
18
ITEM 1
ITEM 10
ITEM 11
ITEM 12
ITEM 18
ITEM 2
ITEM 3
ITEM 300
ITEM 300000
ITEM 7
ITEM 8
ITEM GOLD

The above result is not in order, please suggest me if any solutions?

Regards,
Anil MK
Re: Sorting strings in Order [message #437250 is a reply to message #437249] Sat, 02 January 2010 13:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes it is in order maybe not the order you want.
Now if you want help, you have to clearly define the order you want.
But BEFORE search as this question has been asked so many times that it is not possible you are not able to find the answer by yourself.

Regards
Michel
Re: Sorting strings in Order [message #437251 is a reply to message #437250] Sat, 02 January 2010 13:14 Go to previous messageGo to next message
anil_mk
Messages: 137
Registered: August 2006
Location: Bangalore, India
Senior Member

Dear Michel,

Thanks for your prompt reply.
As you asked the order in which I need to display the result, below is the resultset to be displayed

1
18
ITEM 1
ITEM 2
ITEM 3
ITEM 7
ITEM 8
ITEM 10
ITEM 11
ITEM 12
ITEM 18
ITEM 300
ITEM 300000
ITEM GOLD

Regards,
Anil MK
Re: Sorting strings in Order [message #437253 is a reply to message #437251] Sat, 02 January 2010 13:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you display the only possible cases?
Can there be number before item?
Can there something after number?

As I said, this has been asked and answered many times, why do the answers fit your needs?

Regards
Michel
Re: Sorting strings in Order [message #437256 is a reply to message #437253] Sat, 02 January 2010 13:29 Go to previous messageGo to next message
anil_mk
Messages: 137
Registered: August 2006
Location: Bangalore, India
Senior Member

Yes Michel, all possible cases are included in my resultset.
Can there be number before item? No
Can there something after number? No

Regards,
Anil MK
Re: Sorting strings in Order [message #437259 is a reply to message #437256] Sat, 02 January 2010 13:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Can there be more than 2 parts (that is more that 1 space)?
When there is only one part (that is no space), is this inevitably a number and can't be a not-number string?

Regards
Michel
Re: Sorting strings in Order [message #437266 is a reply to message #437251] Sat, 02 January 2010 15:19 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Its your data. You should ask/analyze the same that Michel Sir has asked in his previous posts.


Here is the Hint: Use TRANSLATE/REPLACE/LPAD/RPAD as per your requirement

Regards
Ved

[Updated on: Sat, 02 January 2010 15:24]

Report message to a moderator

Re: Sorting strings in Order [message #437305 is a reply to message #437266] Sun, 03 January 2010 11:06 Go to previous messageGo to next message
anil_mk
Messages: 137
Registered: August 2006
Location: Bangalore, India
Senior Member

Thanks Ved for your reply.

Regards,
Anil MK

Re: Sorting strings in Order [message #437306 is a reply to message #437305] Sun, 03 January 2010 11:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what about my questions?

Regards
Michel
Re: Sorting strings in Order [message #437311 is a reply to message #437305] Sun, 03 January 2010 12:41 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Anil,
I can see so many follow ups here.To avoid this and to get a quick reply you should describe your requirement well.And this would help you to get the solution you want. You may not get the ready made solution but surely people are here to show you the right path with some hint.
Hope that in your next post you will follow this.


Moreover, please go through the forum guidelines.

Regards,
Ved

Re: Sorting strings in Order [message #437526 is a reply to message #437311] Mon, 04 January 2010 22:49 Go to previous messageGo to next message
anil_mk
Messages: 137
Registered: August 2006
Location: Bangalore, India
Senior Member

Hi Michel,

I tried the same and got it. Thanks for your reply.


Regards,
Anil MK
Re: Sorting strings in Order [message #437552 is a reply to message #437526] Tue, 05 January 2010 00:30 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Would you show the query you used to solve your problem with its output, so that it can help other people.

regards,
Delna
Re: Sorting strings in Order [message #437679 is a reply to message #437256] Tue, 05 January 2010 08:26 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
anil_mk wrote on Sat, 02 January 2010 20:29

....
all possible cases are included in my resultset.
Can there be number before item? No
Can there something after number? No


If so a possible solution could be like the following:

Processing ...
with a as (
	select 'ITEM 18' a from dual union
	select 'ITEM 10' a from dual union
	select '18' a from dual union
	select 'ITEM 1' a from dual union
	select 'ITEM 11' a from dual union
	select 'ITEM 12' a from dual union
	select 'ITEM 7' a from dual union
	select 'ITEM 8' a from dual union
	select 'ITEM 3' a from dual union
	select 'ITEM 300' a from dual union
	select '1' a from dual union
	select 'ITEM 300000' a from dual union
	select 'ITEM 1' a from dual union
	select 'ITEM GOLD' a from dual union
	select 'ITEM 2' a from dual 
)
select a
from a
order by regexp_replace(a,'^([A-Za-z]*\s+)*([0-9]*)$','\1') nulls first,
	to_number(regexp_replace(a,'^([A-Za-z]*\s*)*([0-9]*)$','\2')) nulls first
Query finished, retrieving results...
     A      
----------- 
1           
18          
ITEM 1      
ITEM 2      
ITEM 3      
ITEM 7      
ITEM 8      
ITEM 10     
ITEM 11     
ITEM 12     
ITEM 18     
ITEM 300    
ITEM 300000 
ITEM GOLD   

14 row(s) retrieved


It would be nearly impossible to do it only with TRANSLATE/REPLACE/LPAD/RPAD.

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28424/adfns_regexp.htm#i1010949


Bye Alessandro

[Updated on: Tue, 05 January 2010 08:27]

Report message to a moderator

Re: Sorting strings in Order [message #437702 is a reply to message #437679] Tue, 05 January 2010 12:40 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Nice one Allesandro!
Re: Sorting strings in Order [message #437704 is a reply to message #437679] Tue, 05 January 2010 13:07 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member

SQL> set linesize 125
SQL> select * from t200;

A                                                                                                                            
-----------                                                                                                                  
1                                                                                                                            
18                                                                                                                           
ITEM 1                                                                                                                       
ITEM 10                                                                                                                      
ITEM 11                                                                                                                      
ITEM 12                                                                                                                      
ITEM 18                                                                                                                      
ITEM 2                                                                                                                       
ITEM 3                                                                                                                       
ITEM 300                                                                                                                     
ITEM 300000                                                                                                                  
ITEM 7                                                                                                                       
ITEM 8                                                                                                                       
ITEM GOLD                                                                                                                    

14 rows selected.

Elapsed: 00:00:00.06
SQL> select  a, to_number(
  2   replace(
  3   translate(a,
  4   replace(
  5   translate(a,'0123456789','##########'),
  6   '#'),rpad('#',20,'#')),'#')) b,length(trim(TRANSLATE(a, '0123456789',' '))) a
  7   from t200 order by c nulls first, to_number(
  8   replace(
  9   translate(a,
 10   replace(
 11   translate(a,'0123456789','##########'),
 12   '#'),rpad('#',20,'#')),'#'));
 translate(a,
           *
ERROR at line 9:
ORA-00960: ambiguous column naming in select list 


Elapsed: 00:00:00.03
SQL> 
SQL> select  a, to_number(
  2   replace(
  3   translate(a,
  4   replace(
  5   translate(a,'0123456789','##########'),
  6   '#'),rpad('#',20,'#')),'#')) b,length(trim(TRANSLATE(a, '0123456789',' '))) c
  7   from t200 order by c nulls first, to_number(
  8   replace(
  9   translate(a,
 10   replace(
 11   translate(a,'0123456789','##########'),
 12   '#'),rpad('#',20,'#')),'#'));

A                    B          C                                                                                            
----------- ---------- ----------                                                                                            
1                                                                                                                            
18                                                                                                                           
ITEM 1               1          4                                                                                            
ITEM 2               2          4                                                                                            
ITEM 3               3          4                                                                                            
ITEM 7               7          4                                                                                            
ITEM 8               8          4                                                                                            
ITEM 10             10          4                                                                                            
ITEM 11             11          4                                                                                            
ITEM 12             12          4                                                                                            
ITEM 18             18          4                                                                                            
ITEM 300           300          4                                                                                            
ITEM 300000     300000          4                                                                                            
ITEM GOLD                       9                                                                                            

14 rows selected.

Elapsed: 00:00:00.16
SQL> spool off;


Regards,
Ved

Edit: Formatted

[Updated on: Tue, 05 January 2010 13:12]

Report message to a moderator

Re: Sorting strings in Order [message #437705 is a reply to message #437704] Tue, 05 January 2010 13:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Some need to learn how to format a query to make it easy to read and understand...

Regards
Michel
Re: Sorting strings in Order [message #437708 is a reply to message #437704] Tue, 05 January 2010 13:29 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member

SELECT   a
FROM     t200
ORDER BY Length(Trim(Translate(a,'0123456789',' '))) NULLS FIRST,
         To_number(Replace(Translate(a,Replace(Translate(a,'0123456789','##########'),'#'),
                                     Rpad('#',20,'#')),'#'));


Well, dont say that I am violating the rules by providing direct answers. Smile Its just an alternative.I provided hint initially.

Regards,
Ved

Edit: Code formatted to make it readable

[Updated on: Tue, 05 January 2010 13:34]

Report message to a moderator

Re: Sorting strings in Order [message #437720 is a reply to message #437704] Tue, 05 January 2010 14:45 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Its_me_ved wrote on Tue, 05 January 2010 20:07
 11   translate(a,'0123456789','##########'),
 12   '#'),rpad('#',20,'#')),'#'));
 translate(a,
           *
ERROR at line 9:
ORA-00960: ambiguous column naming in select list 


Excuse me, but - what was the purpose of posting an invalid query? Was it supposed to show something ("don't do it that way, it is wrong!"), or were you just lazy to exclude this part from copy-paste?
Re: Sorting strings in Order [message #437722 is a reply to message #437720] Tue, 05 January 2010 14:52 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
I provided the spooled sql session.It was a typo. You should understand seeing what is the working sql.

Yes, I am too lazy to make changes to the spooled output file for the session.I am having a hard time here while copying the content from sql plus client in 11g. So prefered to spool it and paste the entire content


Regards,
Ved

[Updated on: Tue, 05 January 2010 14:54]

Report message to a moderator

Re: Sorting strings in Order [message #437725 is a reply to message #437251] Tue, 05 January 2010 17:03 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Assuming the data you gave us, the following should work:

SQL> with a as (
  2     select 'ITEM 18' a from dual union
  3     select 'ITEM 10' a from dual union
  4     select '18' a from dual union
  5     select 'ITEM 1' a from dual union
  6     select 'ITEM 11' a from dual union
  7     select 'ITEM 12' a from dual union
  8     select 'ITEM 7' a from dual union
  9     select 'ITEM 8' a from dual union
 10     select 'ITEM 3' a from dual union
 11     select 'ITEM 300' a from dual union
 12     select '1' a from dual union
 13     select 'ITEM 300000' a from dual union
 14     select 'ITEM 1' a from dual union
 15     select 'ITEM GOLD' a from dual union
 16     select 'ITEM 2' a from dual
 17  )
 18  select * from a
 19  order by regexp_substr(a, '^[^ ]*')
 20        , to_number(regexp_substr(a, '[0-9]*$'));

A
-----------
1
18
ITEM 1
ITEM 2
ITEM 3
ITEM 7
ITEM 8
ITEM 10
ITEM 11
ITEM 12
ITEM 18
ITEM 300
ITEM 300000
ITEM GOLD

14 rows selected.
Re: Sorting strings in Order [message #437791 is a reply to message #437722] Wed, 06 January 2010 00:45 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Its_me_ved wrote on Tue, 05 January 2010 21:52
I provided the spooled sql session.It was a typo. You should understand seeing what is the working sql.

Yes, I am too lazy to make changes to the spooled output file for the session.I am having a hard time here while copying the content from sql plus client in 11g. So prefered to spool it and paste the entire content

Never thought about
- restarting the spool when you encountered the error
- editing the spoolfile
- cutting out the erroneous part after pasting it here?

Now you just posted something vague, leaving people to guess what it is you want to show. It defeats the purpose of your post (being to show an alternative solution)
Re: Sorting strings in Order [message #437941 is a reply to message #437791] Wed, 06 January 2010 09:58 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Does this apply to me only? I am not seeing the same suggesion given by you when others commits the mistake. Please go through some of the posts you will find that being done by a moderator
Re: Sorting strings in Order [message #437944 is a reply to message #437941] Wed, 06 January 2010 10:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It does not just apply to you, but I think you'll find that most other people, when it turns out that they've posted code with bugs in it actually fix the bugs, rather than just saying that they're too lazy to be bothered fixing it.

[fix typo]

[Updated on: Wed, 06 January 2010 10:03]

Report message to a moderator

Re: Sorting strings in Order [message #437979 is a reply to message #437944] Wed, 06 January 2010 11:58 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
I saw the same in your post.Hope that you are not too lazy to go and correct that!!

I don't think that one wont look at the sql(after the fix) that have the output.

I dont give value to such posts.

If you have to give a live demo running sqlplus and if there is an error by mistake would you do clear screen and make the sql run?? Such comments I consider as useless!!!


[Updated on: Wed, 06 January 2010 11:59]

Report message to a moderator

Re: Sorting strings in Order [message #438004 is a reply to message #437979] Wed, 06 January 2010 13:29 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
To cut a long story short: in my opinion, you are wrong. It really doesn't take that much effort to edit your message and omit part which is not supposed to be presented to the community. You don't want to confess that, but it is OK - one has to be grown up, realize that nobody is perfect and that mistakes sometimes happen. No big deal, really.

You should've seen what I was doing; gosh, some messages I posted were a complete crap. I was embarrassed, wished I never wrote something that stupid, but ... that's life.

So what's the matter here? Why can't you just accept that what you did wasn't that perfect, that it could have been done better and keep in mind that - in the future - you should try to improve quality of your posts?

I think that this discussion becomes useless - either you'll accept what you've been told, or not. I can live with it. Can you?
Re: Sorting strings in Order [message #438262 is a reply to message #437704] Thu, 07 January 2010 13:01 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
SQL> set linesize 150
SQL> select * from t200;

A                                                                                                                                                     
-----------                                                                                                                                           
1                                                                                                                                                     
18                                                                                                                                                    
ITEM 1                                                                                                                                                
ITEM 10                                                                                                                                               
ITEM 11                                                                                                                                               
ITEM 12                                                                                                                                               
ITEM 18                                                                                                                                               
ITEM 2                                                                                                                                                
ITEM 3                                                                                                                                                
ITEM 300                                                                                                                                              
ITEM 300000                                                                                                                                           
ITEM 7                                                                                                                                                
ITEM 8                                                                                                                                                
ITEM GOLD                                                                                                                                             

14 rows selected.

Elapsed: 00:00:00.11
SQL> 
SQL> SELECT   a,
  2           To_number(Replace(Translate(a,Replace(Translate(a,'0123456789','##########'),'#'),
  3                                       Rpad('#',20,'#')),'#')) b,
  4           Length(Trim(Translate(a,'0123456789',' ')))c
  5  FROM     t200
  6  ORDER BY c NULLS FIRST,
  7           To_number(Replace(Translate(a,Replace(Translate(a,'0123456789','##########'),'#'),
  8                                       Rpad('#',20,'#')),'#'));

A                    B          C                                                                                                                     
----------- ---------- ----------                                                                                                                     
1                                                                                                                                                     
18                                                                                                                                                    
ITEM 1               1          4                                                                                                                     
ITEM 2               2          4                                                                                                                     
ITEM 3               3          4                                                                                                                     
ITEM 7               7          4                                                                                                                     
ITEM 8               8          4                                                                                                                     
ITEM 10             10          4                                                                                                                     
ITEM 11             11          4                                                                                                                     
ITEM 12             12          4                                                                                                                     
ITEM 18             18          4                                                                                                                     
ITEM 300           300          4                                                                                                                     
ITEM 300000     300000          4                                                                                                                     
ITEM GOLD                       9                                                                                                                     

14 rows selected.

Elapsed: 00:00:00.02
SQL> SELECT   a
  2  FROM     t200
  3  ORDER BY Length(Trim(Translate(a,'0123456789',' '))) NULLS FIRST,
  4           To_number(Replace(Translate(a,Replace(Translate(a,'0123456789','##########'),'#'),
  5                                       Rpad('#',20,'#')),'#'));

A                                                                                                                                                     
-----------                                                                                                                                           
1                                                                                                                                                     
18                                                                                                                                                    
ITEM 1                                                                                                                                                
ITEM 2                                                                                                                                                
ITEM 3                                                                                                                                                
ITEM 7                                                                                                                                                
ITEM 8                                                                                                                                                
ITEM 10                                                                                                                                               
ITEM 11                                                                                                                                               
ITEM 12                                                                                                                                               
ITEM 18                                                                                                                                               
ITEM 300                                                                                                                                              
ITEM 300000                                                                                                                                           
ITEM GOLD                                                                                                                                             

14 rows selected.

Elapsed: 00:00:00.05
SQL> spool off


Re: Sorting strings in Order [message #440262 is a reply to message #438262] Thu, 21 January 2010 22:09 Go to previous message
anil_mk
Messages: 137
Registered: August 2006
Location: Bangalore, India
Senior Member

Yes Littlefoot you are right, no one are perfect. By doing mistake we should learn and correct.

Thanks my dear friends for all your support.

Regards,
Anil MK


Previous Topic: How to implement this logic in a stored procedure
Next Topic: HELP needed!
Goto Forum:
  


Current Time: Sun Dec 04 02:55:11 CST 2016

Total time taken to generate the page: 0.11706 seconds