• 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
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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;
}
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic