File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes JDBC with Oracle prob URGENT Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Java Interview Guide this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "JDBC with Oracle prob URGENT" Watch "JDBC with Oracle prob URGENT" New topic

JDBC with Oracle prob URGENT

Amit Punjwani
Ranch Hand

Joined: Jul 10, 2000
Posts: 50
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

Joined: Oct 13, 2000
Posts: 297
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

Joined: Nov 02, 2000
Posts: 195
I think you could also use rtrim:
select * from Customers where rtrim(CustID,' ') = 'C0001'
Daniel Dunleavy
Ranch Hand

Joined: Mar 13, 2001
Posts: 276
I am pretty sure if you rtrim the field, no index will be used which will impact performace.
Tom Hennigan
Ranch Hand

Joined: Feb 25, 2001
Posts: 71
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.

Tom Hennigan<P>Sun Certified Java 2 Platform Programmer
Rahul Rathore
Ranch Hand

Joined: Sep 30, 2000
Posts: 324
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;
I agree. Here's the link:
subject: JDBC with Oracle prob URGENT
jQuery in Action, 3rd edition