How about indexing on padr(MemoField, 250)?
>Hi,
>
>For memo, SUBSTR() is very usefull, but for index i found a problem.
>
>
>CLEAR
>CREATE CURSOR mcur ( FMEM M NOT NULL)
>* i cannot index a SUBSTRING of a MEMO field directly
>INDEX ON SUBSTR(FMEM,5,10) TAG TAG1
>
>An workaround is simple, but it annoying use it for get optimized SELECT
>
>But workaround can have problem.
>
>
>CLEAR
>**********
>CREATE CURSOR mcur ( FMEM M NOT NULL)
>INSERT INTO mcur VALUES ('ab')
>INSERT INTO mcur VALUES ('ac')
>INSERT INTO mcur VALUES ('ac1')
>INSERT INTO mcur VALUES ('ad'+REPLICATE(CHR(255),300))
>* for SUBSTR() INDEX ON check oversize if exist a oversize key on memo values
>INDEX ON SUBSTR(FMEM+'',2,250) TAG TAG1
>
>*********
>
>CREATE CURSOR mcur ( FMEM M NOT NULL)
>* for LEFT() INDEX ON check oversize key, also for empty cursor
>INDEX ON LEFT(FMEM,250) TAG TAGERR
>
>INSERT INTO mcur VALUES ('ab')
>INSERT INTO mcur VALUES ('ac')
>INSERT INTO mcur VALUES ('ac1')
>* for SUBSTR() INDEX ON is build also if expression oversized 240
>INDEX ON SUBSTR(FMEM+'',2,250) TAG TAG1
>* next value have a oversize key
>INSERT INTO mcur VALUES ('ad'+REPLICATE(CHR(255),300))
>* but index is 2 lenght
>? 'b' ,KEYMATCH('b')
>? 'b ' ,KEYMATCH('b ')
>? 'b ' ,KEYMATCH('b ')
>? 'c1' ,KEYMATCH('c1')
>? 'd'+CHR(255) ,KEYMATCH('d'+CHR(255))
>? 'd'+CHR(255)+CHR(255) ,KEYMATCH('d'+CHR(255)++CHR(255))
>* If i reindex, check oversize is not done ( a oversize key exist on field )
>REINDEX
>* now index have key lenght of 240 bytes
>? 'd+CHR(255)x239' ,KEYMATCH('d'+REPLICATE(CHR(255),239))
>? 'd+CHR(255)x240' ,KEYMATCH('d'+REPLICATE(CHR(255),240))
>* but if i search right spaced strings it apply 250 limit on internal pointers and go in a buffer overrun
>? 'b+SPACE(239)' ,KEYMATCH('b'+SPACE(239))
>? 'b+SPACE(240)' ,KEYMATCH('b'+SPACE(240))
>? 'b+SPACE(241)' ,KEYMATCH('b'+SPACE(241))
>? 'b+SPACE(242)' ,KEYMATCH('b'+SPACE(242))
>? 'b+SPACE(243)' ,KEYMATCH('b'+SPACE(243))
>* now point go out of buffer, and VFP Crash
>? 'b+SPACE(244)' ,KEYMATCH('b'+SPACE(244))
>>
>Fabio
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)
