p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

sql_language thread: Interesting coding issue: Separate words in search variable are searched for individually.


Message #1 by javamon@m... on Wed, 6 Mar 2002 15:37:09
See comments below.

Oleg.

-----Original Message-----
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
found.

I never used this keyword but there are something called SOUNDEX function in
SQL.
I think it should fit your needs. Try to use I think that's what are you
looking for.

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:
yoga
iyengar
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
word.
I took firs  % off.

SHOULD FIND:
low
lows

SHOULD NOT FIND
slow
below
$subst('Email.Unsub').


  Return to Index