Solved

Retrive words from a string and compare

Posted on 2003-11-28
7
223 Views
Last Modified: 2012-05-07
FILENAME       KEYWORD
xxx.doc      abc,caa,funny
xyz.txt      abc,cba,funnys
sdfetll.txt      ball,basketball,players,court,
d.doc      bryan,name,hobbies,male,boy

above is a datasets, fields are filename,keyword. store in a ms access table. both filename and keyword fields are string.   i.e.   'xxx.doc'  is a string,  'abc,caa,funny' is a string

if a user input "abc", the program should return filename associated with it , i.e.  xxx.doc and xyz.txt

since the datasets may be very large, the algorithim to retrive the keywords from keyword string and then compare in order to get the associated filename should be efficient.

anyone has the script or can provide some idea? like what data types., method i should use....etc

thank you for enlightening.

0
Comment
Question by:plovel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 9

Expert Comment

by:mocarts
ID: 9837867
hi, plovel :)
just use SQL:
SELECT FILENAME FROM TABLE1 WHERE ','+KEYWORD+',' LIKE '%,abc,%'

delphi example:
procedure TForm1.Button1Click(Sender: TObject);
begin
  ADOQuery1.SQL.Text := 'SELECT FILENAME FROM TABLE2 WHERE '',''+KEYWORD+'','' LIKE ''%,'
    + Edit1.Text + ',%''';
  ADOQuery1.Open;
  if ADOQuery1.RecordCount = 0 then
    ShowMessage('No result found!')
  else
    ShowMessage('FileName: '+ ADOQuery1.FieldByName('FILENAME').AsString);
  ADOQuery1.Close;
end;

wbr, mo.
0
 
LVL 9

Expert Comment

by:mocarts
ID: 9837893
caution - you must check for empty search string, otherwise you will get all records where KEYWORD field ends with comma.
0
 
LVL 3

Expert Comment

by:ahll
ID: 9838061
I agree with mocarts, but you have to include in the KEYWORD field a comma at the beginind and at the end. If not, you will lose the first and the last word.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Accepted Solution

by:
mocarts earned 150 total points
ID: 9838129
not actually - that can be overcome in SQL by "quoting" keyword field in commas - what is actually done in my example.
SELECT FILENAME FROM TABLE1 WHERE ','+KEYWORD+',' LIKE '%,abc,%'

also comment to my example - plovel - you should additionally implement loop (while not eof) for retrieving all selected records (filenames):
var
  sFiles: string;
begin
...
 ADOQuery1.Open;
  if ADOQuery1.RecordCount = 0 then
    ShowMessage('No result found!')
  else begin
    while not ADOQuery1.eof do begin
      sFIles := sFiles + ',' + ADOQuery1.FieldByName('FILENAME').AsString;
      ADOQuery1.Next;
    end;
    if sFiles <> '' then Delete(sFiles, 1);
    ShowMessage(sFiles);
  end;
...
0
 
LVL 1

Expert Comment

by:cerdal
ID: 9887682
You'd have much faster access if you split the list up and inserted one record for each keyword. That way you could just do a straight "select filename from Table1 where keyword = :MyKeyWord".

If the table has other fields in it, which would mean too many duplicates, add an AutoIncrement field to Table1 and fill a second table with KeyWord + Tbl1AutoIncValue in Table1's AfterPost event. You could then do:

Select TABLENAME from TABLE1, TABLE2
Where TABLE2.KEYWORD = :MyKeyWord
And TABLE1.AutoIncValue = TABLE2.Tbl1AutoIncValue

If you need more detail, just ask.

Chris.
0
 
LVL 9

Expert Comment

by:mocarts
ID: 9965421
thanks, plovel :)
small comment on Chris comment..
that will be only a little bit faster, but will take more room to store all data. and if to be truly formal then there must be three tables - one for keywords, one for filenames and last one to link previous tables i.e.
Kyeword table (KT): IDKeyword, Keyword
Filename table (FT): IDFile, FileName
Link table (LT): IDKeyword, IDFile
and select would be SELECT FT.FileName FROM FT, KT, LT WHERE FT.IDFile=LT.IDFile AND KT.IDKeyword=LT.IDKeyword AND KT.Keyword = 'KeywordToSearch'

wbr, mo.
0
 
LVL 1

Expert Comment

by:cerdal
ID: 9967993
reply to mo:

pragmatism is always best :-) If it's good enough, do it that way.

it's another case of horses for courses - I've never worked with Access, but some databases can be orders of magnitude slower matching parts of a field (even if it's indexed) in every record from start to finish rather than going straight to the only ones whose keyfield matches what's needed exactly.

It might be an idea for plovel to compare the 2 methods and let us know how much faster it actually is in his situation...

As for the extent of normalising that's required, I agree it's pretty rare to find a case where 100% is optimum.

Chris.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month9 days, 23 hours left to enroll

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question