>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?
>
>TIA
>
>Agnes
Determine deepest structure
Create dummy field that can store surogate key according to structure dept.
Break each string into substring, pad with zeroes on the left and you get your surogate key
(This would be function)
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
000.000.001.001
001.002.000.000
001.002.00R.000
001.002.00N.000
.
.
.
Index or order subquery by that surogate field
HTH
Sergio
.
.