Share this page 

Use a CHAR field in the WHERE clause in a PreparedStatementTag(s): JDBC


With Oracle, if a CHAR field used in a WHERE clause contains trailing spaces then the trailing spaces must be there, there is no automatic trimming.

For example, if the "code_value" field is defined as CHAR(10) and the content is "A10" then the real value is "A10       " and the trailing spaces must be present in the comparaison to have a match.

The following PreparedStatement will fail to retrieve the value : 

 PreparedStatement ps;
 ps = conn.prepareStatement("SELECT desc_value from prod.DICT_VALUES WHERE code_value= ?") ;
 ps.setString(1,"A10");
 rs = ps.executeQuery();
 while (rs.next())
    System.out.println("results: " + rs.getString(1));
But this one is ok 
PreparedStatement ps;
 ps = conn.prepareStatement("SELECT desc_value from prod.DICT_VALUES WHERE code_value= ?") ;
 ps.setString(1,"A10          ");
 rs = ps.executeQuery();
 while (rs.next())
    System.out.println("results: " + rs.getString(1));
The easy fix is to define the field as a VARCHAR not a CHAR or replace the PreparedStatement with a Statement. 
 Statement s;
 s= conn.createStatement();
 String val = "A10";
 String sql ="SELECT desc_value from prod.DICT_VALUES WHERE code_value='" + val + "'" ;
 ResultSet rs = s.executeQuery(sql);
 while (rs.next())
   System.out.println("results: " + rs.getString(1));
To keep a PreparedStatement, you need to add explicitly the trailing spaces or trim the value.

The first try is to trim the value with the rtrim() function. 

 ps = conn.prepareStatement("SELECT desc_value from prod.DICT_VALUES WHERE rtrim(code_value)= ?") ;
 ps.setString(1,"A10");
 rs = ps.executeQuery();
 while (rs.next())
   System.out.println("results: " + rs.getString(1));
This is working fine but Oracle will not use the index and probably perform a table scan which is not a good thing!

Oracle provides an API to make sure that the parameter received is handled as a CHAR value by the database. 

import oracle.jdbc.OraclePreparedStatement;
...
PreparedStatement ps;

ps = conn.prepareStatement("SELECT dewsc_value from prod.DICT_VALUES WHERE code_value= ?") ;
((OraclePreparedStatement)ps).setFixedCHAR(1, "A10");
rs = ps.executeQuery();
while (rs.next())
   System.out.println("results: " + rs.getString(1));
The good thing is that we don't need to know the CHAR width of the field, the driver will figure it out for us but using the special Oracle class can be problem.

A better solution is to instruct the database to add the missing trailing spaces with the rpad() function. 

 ps = conn.prepareStatement("SELECT desc_value from prod.DICT_VALUES WHERE code_value= rpad(?, 32, ' ') ") ;
 ps.setString(1,"A10");
 rs = ps.executeQuery();
 while (rs.next())
   System.out.println("results: " + rs.getString(1));
This a better solution because we are not using a special Oracle class and the database will use the index.