Index on First Word of Character Field

[SSDESIGN]

What is a way for indexing on the first word of a character field? Tried the following but received an error "Invalid key length":

INDEX ON LEFT(LTRIM(name1), AT(" ",LTRIM(name1),1)-1) TAG name1

If I enter the above index command manually through the editing of indexes, IT WORKS!!!! - both in creating the index and in doing searches with the index...

Thanks...

 

[r0gerc]

May I suggest to try without all the LTRIMming in the index expression and just index on the whole field?

Roger

 

[JRB-Bldr]

You say that the INDEX command works when you execute it manually in the Command Window?

Are you executing it on the same data table as the application does?

If not, then you might want to look at the data in the Name1 field of the REAL data.

Perhaps you have some invalid data or something in the data itself which is causing the error message.

Personally I would simplify the expression slightly and add qualifiers - something like:

INDEX ON LEFT(Name1,AT(" ",Name1)-1) TAG Name1;
FOR !EMPTY(Name1) AND !ISNULL(Name1)

If you might happen to have leading spaces in some of the Name1 fields, you might try eliminating those BEFORE they enter the data table so as to 'standardize' the data.

< In your application code during data entry >
REPLACE Name1 WITH ALLTRIM(Name1)

You might also want to 'normalize' the index expression so that your SEEK's can find UPPER(Name1) and PROPER(Name1) and LOWER(Name1).

Again, something like:
INDEX ON UPPER(LEFT(Name1,AT(" ",Name1)-1)) TAG Name1;
FOR !EMPTY(Name1) AND !ISNULL(Name1)

Then you want to always
IF SEEK(UPPER(cFName))

Good Luck,
JRB-Bldr

 

[cricket]

An index with trimmed fields (from LTRIM, variable LEFT as you propose) has the characteristic that the nature of the first field in the index determines subsequent values.

If the first record index is based on a field containing "ED JONES", then your index is based on 2 characters

If the next 2 records are "PAUL SMITH", and "PABLO PICASSO", then they are treated as equals when indexed to a depth of 2 characters, and the first occurring record will present first

If you create a simple table with 2 fields CNT Numeric(3) and NAME1 Character(20) you will see the problem when indexed as proposed.
CNT NAME1
1 PA XXX
2 PAYE.XXX
3 PADLOCK.XXX
4 PADDING.XXX
5 PAX.XXX

Change NAME1 to PARADIGM_SHIFT XXX in the first record and reindex. You will see a very different sequence


If you have control of the table and programs, just add a new field containing the first word from the character field, which you can fill as described in the earlier posts.

Watch for spaces such as I B M instead of IBM

 

[TamarGranor]

Use PADR() to make sure that you make all the index expressions the same length.

Tamar

 

[SSDESIGN]

Thanks for the assistance....

Sometimes it's better to bypass a battle in order to win the war..

I did the following:
1 I removed any leading spaces in the field.
2 I used the FOR statement above - FOR !EMPTY(Name1)AND ! ISNULL(Name1.
3 Indexed on the first 8 positions in the field.

And it worked...
I'm planning to come back and test the indexing option above by jrbbldr.

Again, thanks....