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, but using a vendor specific function is not great..

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.
mail_outline
Send comment, question or suggestion to howto@rgagnon.com