Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Trim Spaces in Criteria and Expression

 
Saket Barve
Ranch Hand
Posts: 229
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:


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:


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


I get proper result.

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

Thanks,
Saket
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you put a '%' before and after you have wildcards, or you can use the MatchMode enum.

Mark
 
Saket Barve
Ranch Hand
Posts: 229
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 229
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 229
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:



with HQL as indicated below:



Saket
[ January 04, 2007: Message edited by: Saket Barve ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic