Indexing on alpha if a list starts with numeric

[tilltek]

I have a data file holding info as follows.....

Apples
Bananas
Carrots
1 Apples
2 Apples
1 Bananas
12 Bananas
9 Carrots

....and so on.
Note that some fields start with a character, some with one numeric, a space and then a character while others might be two numeric, a space and then a character.
My problem is, how do I index on the first alpha character so that the list would be….
Apples
1 Apples
Bananas
1 Bananas
12 Bananas
Carrots
9 Carrots
that is, first on the alpha then on the leading numeric (if there was one).
I can’t change the data file so it seems some sort of indexing is the way to go.
Thanks in advance.
Ken F
Brisbane Aust.

 

[dgrewe]

do not know if this will work BUT
index on iif(alpha(field), field, substr(field,at(" ", field)+1)) tag xxxxxxxx David W. Grewe
Dave@internationalbid.com

 

[CAUFred]

dgrew is close, use "ISALPHA" in the IIF statement to index on a string starting with an alpha character as "TRUE". You also will have to include your numeric characters on the end of the "FALSE" substring with a "+LEFT(field,n)" so the string that is being indexed will look like "Apples" for the "TRUE" and "Apples1" for the "FALSE".
It does work.....
IIF(ISALPHA(field),field,alltrim(substr(field,at(" ",field)))+(left(field,n))

I do the "ALLTRIM" to get the "gas" off the string I am working with.

 

[dgrewe]

Thanks CauFred, I was rushed and did not double check it before I left the site, adding Upper() might help with the consistency of the seek,

David W. Grewe
Dave@internationalbid.com

 

[wgcs]

Try this:

INDEX ON ALLTRIM(CHRTRAN(fieldname, "0123456789", "&quot) TAG whatever

 

[tilltek]

Thanks once again to Tek-Tips and the gurus who sail her. Both sugestions worked and I must say I would not have thought of either by myself.
Ken F