• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Jeanne Boyarsky
  • Ron McLeod
Sheriffs:
  • Paul Clapham
  • Liutauras Vilda
  • Devaka Cooray
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
Bartenders:

JDBC with Oracle prob URGENT

 
Ranch Hand
Posts: 50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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....

 
Ranch Hand
Posts: 297
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 195
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I think you could also use rtrim:
select * from Customers where rtrim(CustID,' ') = 'C0001'
Brian
 
Ranch Hand
Posts: 276
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am pretty sure if you rtrim the field, no index will be used which will impact performace.
 
Ranch Hand
Posts: 71
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 324
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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;
}
 
machines help you to do more, but experience less. Experience this tiny ad:
Smokeless wood heat with a rocket mass heater
https://woodheat.net
reply
    Bookmark Topic Watch Topic
  • New Topic