• 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:
  • Tim Cooke
  • Campbell Ritchie
  • Ron McLeod
  • Junilu Lacar
  • Liutauras Vilda
Sheriffs:
  • Paul Clapham
  • Jeanne Boyarsky
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Piet Souris
  • Carey Brown
Bartenders:
  • Jesse Duncan
  • Frits Walraven
  • Mikalai Zaikin

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 ]
 
pie. 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