Re: Searching fields

Can't remember, does LOOKUP() have something like this for indexed fields?

I suppose you could dump the field contents, with an index key, into a text file
and search that; but I just do something like this:

  'fld$ contains the field you want to search
  'find$ contains the string to find

  FILE "foo"
  DIM recnums%(RECCOUNT ("foo"))' capture records containing the
string
  INDEX recnum
  'filter out empty fields
  EXECUTE "SELECT WHERE (" + fld$ + " <> " + CHR$(34)
+ CHR$(34) + ")"
  SELECT FIRST FILE "foo"
  WHILE NOT EOF ("foo")
    IF (INSTR (fld$$,find$)) THEN recnums$(i%) = recnum.FOO:i% = i% + 1
    SELECT NEXT FILE "foo"
  WEND

You could try
  IF (fld$$ LIKE ("*" + find$ + "*")) THEN etc. to see what
is faster...
  Apply whatever case-sensitivity techniques you like, UCAS$(), LIKE, =, etc.

  There is also a keyword, CONTAINS, that might be useful somewhere...

Peter


On Mon, 31 Jul 2006 06:33:20 -0400, Ian Stephenson <ian@soeasy.co.nz>
wrote:

> Hi everyone
> I need to search for a string in a full field of all records:
> E.g.
> Text.FRETAIL = "This is the information stored in a field"
>
> Action = Find all records with the word "stored"
>
> Any ideas?
>
> Cheers
> Ian
>
>
>
>
>
>
>

Ian Stephenson wrote:
> Hi everyone
> I need to search for a string in a full field of all records:
> E.g.
> Text.FRETAIL = "This is the information stored in a field"
> 
> Action = Find all records with the word "stored"

If you need to do this often and it is only one field, then you may want
to set up a full text search on that field. That would require a
database file for the index entries (1 entry per target record per
word), possibly an exclusion list (if you need that), plus functions to
parse the words from an entry and add them to the table. Another to
delete entries when a record is deleted. When editing an entry, if the
user saves, just delete what is already there and then reparse the field
and create new entries. That is for th ultra fast version. If you just
want what you described above, and there aren't too many records in the
file, then you can use:

LIKE "*stored*" in your WHERE clause.

Hi everyone
I need to search for a string in a full field of all records:
E.g.
Text.FRETAIL = "This is the information stored in a field"

Action = Find all records with the word "stored"

Any ideas?

Cheers
Ian

Hi and thanks for your ideas.

I eventualy used the concepts you discussed and arrived at the following 
solution.
Anyone have any ideas on how it could be made faster?
The idea is that a field containing some part of the text required will be 
copied into
a temporay file for the user to select

e.g. "This is a Big nut"
       "This is a small nut"
       "This is nothing"

Search for This and 3 entries will show
Search for Nut and two entries will show



============================================

  REQUEST "Please enter a word you would","like to search the
product 
register for.",4,ansd%%,a$
    IF ansd%% = 0 THEN END SUB

    MOUSE OFF
    REM Make/open a temporary file = PPROTEMP to store the results
        Dir$ = DIRECTORY :DIRECTORY DIRTEMP$
        Fail$ = ""
        IF EXISTS ("FPROTEMP.SBF") THEN
             OPEN FILE SHARE ,8"FPROTEMP"
             REMOVE FILE "FPROTEMP"
        END IF

        CREATE "FPROTEMP"
        ADD "Descript;TXT IXD;40",""
        ADD "OpCode;TXT IXD;20 U",""
        MAKE "FPROTEMP"

        DIRECTORY Dir$

   REM Set some vars for statistical reporting at the end of the procedure 
and set up the file we want to search through
        Mtchs% = 0
        a% = LEN (a$)
        FILE "FPRODUCT":INDEX "Descript"
        q% = RECCOUNT ("FPRODUCT")
        Tstart% = NOW

   REM Go through the fields you want to test for the search string. If 
found make an entry into the temp file
    SELECT FIRST
    WHILE NOT EOF ("FPRODUCT")
          b% = LEN (Descript.FPRODUCT):d% = b% - a% + 1
          IF b% >= a% THEN
             FOR c% = 0 TO d%
                 IF ( MID$ (Descript,c%,a%)) LIKE a$ THEN
                    BLANK FILE "FPROTEMP":Descript.FPROTEMP = LEFT$ 
(Descript.FPRODUCT,40):OpCode.FPROTEMP = LEFT$ (OpCode.FPRODUCT,20):STORE 
FILE "FPROTEMP":c% = d%:Mtchs% = Mtchs% + 1
                 END IF
             NEXT
          END IF
          SELECT NEXT :q% = q% - 1
    WEND
    TEnd% = NOW

 REM Display the result in a selectable dialog box and place the users 
selection in the required code inside OpCode$
RTYADVPSRCH:
    OpCode$ = ""
    REQUEST "Done in " + TIME$ (TEnd% - Tstart%,"hh:mm:ss")
+ " Matches = " 
+ STR$ (Mtchs%,"99999"),"Please select a 
product",24,ansd%%,OpCode$,72,OpCode.FPROTEMP,Descript.FPROTEMP, INDEX 
OpCode.FPROTEMP

This procedure works really well and has provided so many new functions that 
help users find things fast.