>Am I correct in assuming that the proper, and only way, to search for a string CONTAINED within a given field is to use the CONTAINS operator within my SQL statement like this:
>
>SELECT * FROM nameplat WHERE ALLTRIM(UPPER(lcSearchString)) $ (field_searched).
>
>And does "field_searched" have to be an actual fieldname, or can it be a variable that is used to reference any of several fields that the user can choose from.
>
>I am trying to give the user the ability to do "fuzzy searches" on a few select fields.
>
>Thank You.
Elgin,
Official (whatever that means) SQL operator is LIKE. ie:
lcSearchExpr = '%'+ALLTRIM(UPPER(m.lcSearchString))+'%'
SELECT * FROM nameplat WHERE field_searched like m.lcSearchExpr
If you'd do the search on multiple fields:
SELECT * FROM nameplat WHERE field_searched1+field_searched2+field_searched3 like m.lcSearchExpr
VFP specific functions make your life easier eliminating the case sensitivity problem (SQL server for example does case insensitive searches by default - settable):
SELECT * FROM nameplat WHERE atc(m.lcSearchString,field_searched) > 0
Multiple:
SELECT * FROM nameplat WHERE atc(m.lcSearchString,field_searched1+field_searched2) > 0
This way or that way don't forget these type of searches would be slow (unless used tricky implementations or external helper programs like phdbase-does it still exist?).
Cetin