>Hi all,
>
>I have I problem sorting records.
>
>I have an alpanumeric structure that should be sortet numerical. It contains digits and alphas in various compositions.
>example
>
>1.1
>1.02
>10.1
>01.3
>1.02.N
>1.02.N.R
>1.10.R
>1.10.R.1
>20DP
>20
>1.02.R
>
>
>The string lenght is limited by field lenght anything else is possible.
>There is a "normal" that means to separate anything by a point but as the example shows sometimes its ignored.
>
>So what I need is to order the stuff numerical.
>
>1.1
>1.02
>1.02.R
>1.02.N
>1.02.N.R
>01.3
>1.10.R
>1.10.R.1
>10.1
>20
>20DP
>
>
>Has somebody done things like this?
One other approach which I think would work (providing no numeric field exceeds 229): Convert to an ASCII string and index on that. ie.
Translate each numeric field to a character of that value and each alphabetic character to its ASCII value + 165 and add a CHR(0) on the end. So
1.1 = CHR(1)+CHR(1)+CHR(0)
1.02.N.R = CHR(1)+CHR(2)+CHR(ASC("N")+165)+CHR(ASC("R")+165)+CHR(0)
I *think* indexing on this gives the right result. Haven't thought through the actual code to translate this but it should be at least as simple as using a stack of PADL() and PADR() stuff......