Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JDBC with Oracle prob URGENT

 
Amit Punjwani
Ranch Hand
Posts: 50
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi everyone,
I am working on a project on JSP/Servlets with Oracle as the database. I have a field Custoner ID (char)(10)(PK) in the Customer table of the database. so now for e.g if the value is C001 there are 6 white spaces along with C001.
So when i try to search the value C001 in SQL query,
for e.g "select * from Customers where CustID = 'C0001'"
But I do not receive any records matching with C001, coz in the database there is C001 and 6 white spaces
Lemme know what is the solution to this..
Thanx in adv....

 
Michael Hildner
Ranch Hand
Posts: 297
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think the best solution would be to get rid of the white space, but if you need it, change your SQL statement to
"select * from Customers where CustID LIKE 'C0001%'"
This will find all records that START with C0001. There may be better ways to do this in SQL, but not sure.
 
Brian Nice
Ranch Hand
Posts: 195
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think you could also use rtrim:
select * from Customers where rtrim(CustID,' ') = 'C0001'
Brian
 
Daniel Dunleavy
Ranch Hand
Posts: 276
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am pretty sure if you rtrim the field, no index will be used which will impact performace.
 
Tom Hennigan
Ranch Hand
Posts: 71
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Are you using jdbc 'select' or Oracle SQL/plus 'select'
In either case, the spaces should not matter, in my experience. What version of Oracle? I used only Oracle8i, jdbd sql statements and widely varying field lengths. Good luck.
 
Rahul Rathore
Ranch Hand
Posts: 324
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why not append the empty spaces BEFORE passing to the where clause? There can be many ways of doing it. I think one way can be as follows:-

public ResultSet findByCustomer(String custID) {
if(custID.length()>10) throw new IllegalArgumentException();
char[] dest= {' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' '};
char[] src=custID.getChars();
System.arraycopy(src, 0, dest, 0, src.length);
String paddedCustID=new String(dest);
ResultSet rs=stmt.executeQuery("SELECT * FROM customers WHERE custid='" + "paddedCustID" + "'");
return rs;
}
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic