sorting by and using charAt

[orelia hans]

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]

use something like:

order by integer(substr(ProjectNumber, 2))

[jeroen riezebeek]

  
"SELECT * FROM Projects WHERE Status = '" +st+"' and EntryType='"+e+"' order by projectnumber asc";

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

[Mark Spritzler]

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 ]