aspose file tools*
The moose likes JDBC and the fly likes sorting by and using charAt Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "sorting by and using charAt" Watch "sorting by and using charAt" New topic
Author

sorting by and using charAt

orelia hans
Ranch Hand

Joined: Jan 29, 2004
Posts: 37
I am using this query to get projects from my tables:
String sql = "SELECT * FROM Projects WHERE Status = '" +st+"' and EntryType='"+e+"' ";
The projects are coming sorted ou like P1 then P10 then P11,then P2 then P9,ect. i would like to be able to have P1, P2,P9,P10,P11. How do I do that with the code I already have. I want to sort by ProjectNumber but only with the second character witch is a number instead of considering the all thing as a string.
Thanks
Jason Davies
Greenhorn

Joined: Sep 18, 2003
Posts: 14
use something like:


<a href="http://www.netspade.com/" target="_blank" rel="nofollow">http://www.netspade.com/</a> - Web programming articles and tutorials.
jeroen riezebeek
Greenhorn

Joined: Nov 26, 2002
Posts: 9

Let the database do the sorting. So include a sort statement into your query.
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17259
    
    6

jeroen, that is how orelia is ordering, and as you can see since ProjectNumber is a character, the sorting by the database comes out wrong, it puts numbers like this.
This is a common issue, which I believe Jason answered. Which database he is using, I am not sure. In Oracle I would use the to_number() function, instead of the integer() part, you still need the substr().
1
10
11
2
3
4
5
6
7
8
9
OK, on another note, this is not a Servlets question. This forum is for Servlets question. We have some great other forums for many topics, and it is best to post your question in the forum that best fits your topic to match our topics.
I believe we can move this thread to the JDBC forum, even though I think the Oracle forum would have been even better, but I cannot guess that orelia has Oracle.
Mark
[ April 05, 2004: Message edited by: Mark Spritzler ]

Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: sorting by and using charAt