>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.
regards
thomas