Need help searching multiple words in a database...

[mrdevildog]

Alright Delphi gurus here's one for ya:

I've got a dbgrid connected to a database through an ADOQuery. I have the user input a word or string into an edit box which searches a specific field. I clear the ADOQuery (sql.clear) and then run a sql.add on it. The problem is that the search will only look for that word or string (such as 'water' or 'water rights'). I want the user to be able to input something like 'water & rights' so that it will search for both 'water' and 'rights' not just 'water rights'. Any ideas would be appreciated!!!

marz

 

[LucieLastic]

select * from mytable
where field like '%water%'
or field like '%rights%'

You'd have to strip out the '&' from the input string. If you wanted the input something like 'water & NOT right', remove the & and check which word the NOT is associated to and build the query like this:

select * from mytable
where field like '%water%'
AND not (field like '%rights%')


You could use a filter on your dataset and save you keep rewriting the query.sql property. You can't use LIKE in a filter I don't think, you use '*right*' instead.

lou

 

[mrdevildog]

thx lou...

the only problem is, how would I go about getting those words into an sql statement - I mean, what if the person inputs 3 words, or 4 words. I guess I was a little unclear about that in my question. How would I go about modifying the sql statement to be flexible enough to handle one, two, or many words.

select * from mytable
where field like '%water%' //I may want to stop here....but
AND not (field like '%rights%')//may want to stop here or
...............................//how bout another one...

I hope that makes more sense...thx

 

[LucieLastic]

Firstly, parse your input string and retrieve the 'search' words and store each word in a stringlist.

with qry do
begin
sql.add('select * from mytable')
if stringlist.count > 0 then
begin
sql.add('where');
for idx := 0 to stringlist.count-1 do
begin
sql.add(' field like ''%'+stringlist[idx]+'%''');
if idx< stringlist.count -1 then
sql.add('or');
end;
end;

To do it using a filter instead of a TStringlist and rewriting the query, you need something like this:-

qry.filtered := true;
qry.filter := '';
for idx := 0 to stringlist.count-1 do
begin
qry.filter := qry.filter + '(field = ''*'+stringlist[idx]+'*'')');
if idx< stringlist.count -1 then
qry.filter := qry.filter + ' or ');
end;

Hope this helps.

lou

 

[LucieLastic]

Typo in last message, you'll need to use a TStringlist in both cases.

You could make your input string parser quite sophisticated, eg 'water rights' AND ('other' OR 'wotsit') and you'd need some thinking in your filter/condition building.

Using LIKE in your sql is, I think, case insensitive. If it isn't and you want it to be, change the appropriate line to


sql.add(' Upper(field) like ''%'+Uppercase(stringlist[idx])+'%''');

if your db doesn't like Upper, it may be slightly different, eg ToUpper or something.

What db are you using?

 

[mrdevildog]

Thx!! Everything worked great!!! The only thing that stumped me for awhile was that the dbgrid.datasource.dataset.active went false after changing the adoquery statement. by the way, it's running on an access database...which is being migrated to sqlserver.

thx again

 

[LucieLastic]

hi mrDevilDog

If you change it to use the Filter property instead, your grid won't go blank as the query will not be closed. It may also be faster.

lou