Look for whole word when using HQL

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(?) " );

Who is Participating?
Nguyen Huu PhuocConnect With a Mentor Senior ManagerCommented:
Hi mshirazi1!
If I call keyword is a string keyword which your users enter it.
sql.append("upper(sc.name) like "+"'%"+upper(keyword)+"%'");
will return the rows which contain the keyword.
Take example:
it return the rows which have the following sc.name:
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
Nguyen Huu PhuocSenior ManagerCommented:
just use
sql.append( " upper(sc.name) like spa% " );
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('%?%') " );
Nguyen Huu PhuocSenior ManagerCommented:
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.
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.