Off the top of my head, without testing
SELECT PADL(ALLTRIM(field), LEN(field), "0") AS SortOrd, field
FROM table
ORDER BY SortOrd
>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
Craig Berntson
MCSD, Microsoft .Net MVP, Grape City Community Influencer