First, don't use ALLTRIM() in the index because VFP padds index expression to the fixed lenght anyway. Second, index could help only in finding plates that begins with specified string. I don't have a ready solution, but if plate number isn't to long, you can create separate index for parts of the plate number.
INDEX ON platenum TAG p1
INDEX ON SUBSTR(platenum,2) TAG p2
....
INDEX ON SUBSTR(platenum,7) TAG p7
...
SELECT * FROM mytable ;
WHERE platenum = lcString ;
OR SUBSTR(platenum,2) = lcString ;
...
OR SUBSTR(platenum,7) = lcString ;
INTO CURSOR crsResult
>High all!
>
>I am trying to search through a 1 million record table for a partial license plate number. Someone may know 3 letters of the plate number and those three letters could be anywhere in the field. I am experimenting with the code below but it is too slow. I have used all of the AT() commands and Occurs().
>The table is indexed on ALLTRIM(plate_no). Pretty much the same results. Any help would be greatly appreciated.
>
>
>clear
>IF !USED('mvr')
>USE D:\wconnect\data\mvr\mvr.dbf IN 0 SHARED
>ENDIF
>SELECT mvr
>locate
>SCAN
>lcRec = STR(RECNO())
>WAIT lcRec WINDOW nowait
>lcTag = ALLTRIM(plate_no)
>lcString = "C234"
>DO case
>CASE atcc(lcString,lcTag)>0
>?lcTag
>ENDCASE
>ENDSCAN
--sb--