File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases 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 and Relational Databases
Bookmark "sorting by and using charAt" Watch "sorting by and using charAt" New topic

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.
Jason Davies

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

<a href="" target="_blank" rel="nofollow"></a> - Web programming articles and tutorials.
jeroen riezebeek

Joined: Nov 26, 2002
Posts: 9

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

Joined: Feb 05, 2001
Posts: 17276

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().
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.
[ April 05, 2004: Message edited by: Mark Spritzler ]

Perfect World Programming, LLC - iOS Apps
How to Ask Questions the Smart Way FAQ
I agree. Here's the link:
subject: sorting by and using charAt
jQuery in Action, 3rd edition