Hi,
>>As others have stated it's probably best to change the format at entry. But you *could* use an index. Dragan's example would work:
>>
>>INDEX ON left(yourkey,2)+transform(val(subs(yourkey,3)), "@L 999") TAG xxx
>>* except it should be :
>>INDEX ON left(yourkey,3)+transform(val(subs(yourkey,4)), "@L 999") TAG xxx
>>
>>You'd still need to enforce the 'three-character followed by up to three digit' rule though.
>
>Enforcing such rules down to fixed length is too errorprone IMHO. A rule slightly less likely forgotten/to get broken might be "a couple of letters [1..n] followed by a number of digits [1..m]". You could build your index padr(..., n) + Padl(..., m, "0") and dynamically blend out either chars or digits from the field. No unwanted surprizes if somebody goes for a "XX1" key.
>
>Probably best is to bite the bullet NOW and separate char and digit info into separate fields (as this might be a step leading to better normalization - dunno your model) only adding them together for the index.
>
TBH, I'm not a fan of using multiple fields for a PK (always assuming this is one of course). And if you stick to one field then whether you reformat the field or bodge an index on the existing format you still have to enforce the rules for subsequent data entry.
, Best,
Viv