Trim Spaces in Criteria and Expression

[Saket Barve]

Hello everyone, a very Happy and Prosperous New Year to all.

I am using Criteria and Expression to create a search query, relevant part of which listed below:

    
 
Criteria criteria = session.createCriteria( Patternstyle.class );
criteria.add( Expression.ilike( "styleCd", searchName ) );
 

This query seems to take the trailing spaces present in the database field into account when fetching the results thereby giving improper resultset.

For example:
If I set the searchName to:

    
 
searchName = "J001";
 

I get zero results. However, if I set the searchName to:

  
 
searchName = "J001     "; //Copy and Paste the field value from DB
 

I get proper result.

How can I handle the trim using the current query format?

Thanks,
Saket

[Mark Spritzler]

If you put a '%' before and after you have wildcards, or you can use the MatchMode enum.

Mark

[Saket Barve]

Thanks for the response, Mark.

If you put a '%' before and after you have wildcards

I thought % itself is a wildcard? Simply putting in % does not help because it returns every string starting with "J001".

or you can use the MatchMode enum

Is there a sample example that I can refer to? I am also trying to google but haven't made much progress yet.

Saket

[Lakshmi Dasari]

Saket,
Firstly is "J001 " a valid value in your application? if not then its just that the database is not consistent. Perhaps you could think of trimming the data before inserting into the database table and query by "J001". Hope this helps

[Saket Barve]

Well, Lakshmi I can surely ensure that new but inconsistent data doesn't go into the database by doing what you mention and am already doing so. This however, doesn't help resolve the issue I am presently facing due to the data which already exists in the database.

Thanks,
Saket

[Mark Spritzler]

Originally posted by Saket Barve:
Well, Lakshmi I can surely ensure that new but inconsistent data doesn't go into the database by doing what you mention and am already doing so. This however, doesn't help resolve the issue I am presently facing due to the data which already exists in the database.

Thanks,
Saket

would you be able to run an update statement on the data currently in the database to remove the spaces?

Mark

[Saket Barve]

Yes, Mark that can and has been done. Interestingly, I found out that the query executes as desired when using HQL instead of Criteria.

The solution, for those facing the same problem, is replacing the existing code:

   
 
Criteria criteria = session.createCriteria( Patternstyle.class );
criteria.add( Expression.ilike( "styleCd", searchName ) );
 

with HQL as indicated below:

   
 
Query query = session.createQuery( "from  Patternstyle ps where LOWER( ps.styleCd ) = :styleCd " );
query.setString( "styleCd", searchString.toLowerCase() );
 

Saket
[ January 04, 2007: Message edited by: Saket Barve ]