sql_language thread: Interesting coding issue: Separate words in search variable are searched for individually.
See comments below.
From: javamon@m... [mailto:javamon@m...]
Sent: March 06, 2002 3:37 PM
To: sql language
Subject: [sql_language] Interesting coding issue: Separate words in
search variable are searched for individually.
This is a good question for when you are sipping coffee or tea and are
inspired to fiddle with some code. I require additional functionality for
my SQL search:
MY CURRENT SEARCH
SELECT ID, Authors, Year, Title FROM libraryTable
WHERE (Authors LIKE '%" + varAuthor + "%')
OR (Keywords LIKE '%" + varKeyword + "%')
OR (Title LIKE '%" + varTitle + "%')";
ORDER BY Year;
ADDITIONAL FUNCTIONALITY REQUIRED
1) The search should be able to locate singular words when the plural (s
or es) is entered.
Example: If "singers" is entered in the search box, than "singer" would be
I never used this keyword but there are something called SOUNDEX function in
I think it should fit your needs. Try to use I think that's what are you
2)When two or more words are entered in search box (for example, into the
variable varTitle), each word entered should be searched for separately.
So if "Iyengar Yoga" is entered, than the records found might contain:
India yoga style iyengar
yoga tai chi iyengar
Hmm, Here I think you should work around a little and prepare your WHERE
clause in the follow way.
Following you example it can be look like :
OR (Title IN ('" + varTitle1 + "','" + varTitle2 + "','" + varTitle3 + "')";
I don't know if you can combine LIKE and IN close together. If not I think
you'll have to think of creating dynamic query. It will require you
to write additional code before you send your SQL to Database.
3)The search should NOT locate words in which the search word is merely a
component. So if "low" is entered in the search box:
This one is easy. Just change your WHERE clause to
WHERE (Authors LIKE '" + varAuthor + "%')
OR (Keywords LIKE '" + varKeyword + "%')
OR (Title LIKE '" + varTitle + "%')";
It should solve this problem. It will take only whatever begins with this
I took firs % off.
SHOULD NOT FIND