[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1103
  • Last Modified:

Look for whole word when using HQL

Hi
When I look for spa for exmaple, my code goes and grabs things like spanish
How am I supposed to tell it to look for the whole word. I tried adding space at the end but didn't work
How am I supposed to do it without adding extra space to the end of my query. In other words how do I specify in hql to look for the whole word.
Here is the portion of the code I have:
 sql.append(")   or (  ");
               for ( int i = 0; i < field.length; i++ ) {
                  sql.append( " upper(sc.name) like upper(?) " );
                  if (i < field.length-1)
                     sql.append(" and ");
               }
This is the line where I am having the issue. I don't know how to say hql to look for the whole word so when someone types spa,spanish doesn't come up.
  sql.append( " upper(sc.name) like upper(?) " );

Thanks,
Ross
0
mshirazi1
Asked:
mshirazi1
  • 3
  • 2
1 Solution
 
phuocnhCommented:
just use
sql.append( " upper(sc.name) like spa% " );
Phuoc
0
 
mshirazi1Author Commented:
I trid the followings and no result comes up
sql.append( " upper(c3.name) like upper(?%) " );
sql.append( " upper(c3.name) like upper('?%') " );
sql.append( " upper(c3.name) like upper(%?%) " );
sql.append( " upper(c3.name) like upper('%?%') " );
                 
                                   
0
 
phuocnhCommented:
Why do you use upper(?%),upper('?%'),upper(%?%),upper('%?%')?
Do you can list the values of c3.name?
If you don't know, I have no idea any more.
Phuoc
0
 
mshirazi1Author Commented:
The variables from upper are the keywords that the user puts in to the keyword search in our website.
There is no way for me to predict the keywords and list them with %. If user type in the word Spa for example, I don
t want words like spanish to be part of a result.. The lookup line happend in the following line:
sql.append( " upper(sc.name) like upper(?) " );
Is there any changes that I can add to the line so that it doesnt bring up Spanish any more as a part of the result. Spa vs Spanish is just an example from many other words.
In other words, I want a generic solution. Someone suggested
sql.append( " upper(sc.name) like spa% " );
but, even if it works, it's only for the spa keyword.
Thanks,
Ross
0
 
phuocnhCommented:
Hi mshirazi1!
If I call keyword is a string keyword which your users enter it.
Now
sql.append("upper(sc.name) like "+"'%"+upper(keyword)+"%'");
will return the rows which contain the keyword.
Take example:
keyword=ngl
it return the rows which have the following sc.name:
English
ENGLISH
Nglished
If you doesn't want it returns the rows which doesn't contain the keyword,
you use "not like" condition.
You can read more http://www.techonthenet.com/sql/like.php
Phuoc
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now