plovel
asked on
Retrive words from a string and compare
FILENAME KEYWORD
xxx.doc abc,caa,funny
xyz.txt abc,cba,funnys
sdfetll.txt ball,basketball,players,co
d.doc bryan,name,hobbies,male,bo
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.
xxx.doc abc,caa,funny
xyz.txt abc,cba,funnys
sdfetll.txt ball,basketball,players,co
d.doc bryan,name,hobbies,male,bo
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.
caution - you must check for empty search string, otherwise you will get all records where KEYWORD field ends with comma.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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.
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.
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.
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.
just use SQL:
SELECT FILENAME FROM TABLE1 WHERE ','+KEYWORD+',' LIKE '%,abc,%'
delphi example:
procedure TForm1.Button1Click(Sender
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('FIL
ADOQuery1.Close;
end;
wbr, mo.