• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

sorting by and using charAt

 
orelia hans
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
use something like:
 
jeroen riezebeek
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Let the database do the sorting. So include a sort statement into your query.
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic