exact match searches 1

[samsnead]

I have a dbf with a number of alpha fields where I want to search the dbf but only report those records where I get an exact match on a name or a character string. (e.g searching for SHARP but do not want records that have SHARPER, SHARPNESS etc.) Some of the alpha fields will contain multiple words so cannot match the field to the search word. IS there anything that will allow this type of search. I know set exact on would match the field to the word but wouldn't work for memo or multiple word fields

 

[bborissov]

CREATE DBF MyTable (Fld1 I, MyMemo M)
INSERT INTO MyTable VALUES (1, [SHARP is what you search])
INSERT INTO MyTable VALUES (1, [You search for SHARP])
INSERT INTO MyTable VALUES (1, [But where is SHARP is these fields])
INSERT INTO MyTable VALUES (1, [I don't want  SHARPNESS])

SELECT *;
       FROM MyTable;
       WHERE MyMemo LIKE "SHARP %"   OR;
             MyMemo LIKE "% SHARP %" OR;
             MyMemo LIKE "% SHARP"

But be carefull this kind of queries are extremely slow.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[samsnead]

Excellent Borislav - thx. To speed up I am going to first select using $ then use your code to select from that result.

I have to modify slightly as sometimes a name might end with a comma (eg Borissov, Smith, Brown) so will be even slower as the select becomes

SELECT *;
FROM MyTable;
WHERE MyMemo LIKE "SHARP %" OR;
MyMemo LIKE "% SHARP %" OR;
MyMemo LIKE "% SHARP" OR;
MyMemo LIKE "SHARP,% etc.

 

[Olaf Doschke]

if you often search you may create a table with keywords and update this with each insert/update of the memo field. Rebuild that keyword table every now and then.

In the process of creating that keyword table you can use Getwordnum() or ALINES() and beforehand replace each "," "." or such characters with a SPACE. Now you can first search the word in the keyword table with an exact match. Have some other tables, which does an n:m relation to the table containig the memo and you have your result.
This will sow down edit/insert into the table slightly, but spped up searches very good.

Bye, Olaf.