• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Jeanne Boyarsky
  • Liutauras Vilda
Sheriffs:
  • Rob Spoor
  • Bear Bibeault
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Piet Souris
Bartenders:
  • Frits Walraven
  • Himai Minh

Trim Spaces in Criteria and Expression

 
Ranch Hand
Posts: 237
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
ranger
Posts: 17346
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 237
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 237
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 17346
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 237
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic