How do I sort a character field containing number with and without deciamals

[Ronald Anusiewicz]

I have a character field in a table that contains a number. Right now the sort order is:

1
10
11
12
2
3
4
5
5.1
6
6.1
7
8
8.1
9
9.1

I would like the sort order to be:

1
2
3
4
5
5.1
6
6.1
7
8
8.1
9
9.1
10
11
12

Using padl() I can make it work until I add the decimal numbers. If at all possible, I really don't want to add ".0" to each number.
Almost all the entries will be just a whole number. Very few will have the decimal and those that do will usually be just a ".1".
I guess another option would be to add an "a" but then I think I really have the same problem.

Thanks,

[Tim Kiebert]

Re: How do I sort a character field containing number with and without deciamals

You obviously have a reason for it to be character and not numeric.

If possible in your situation use val(yourfieldname)
Maybe add a calculated field using the above and sort on that.

[johnkoh]

Re: How do I sort a character field containing number with and without deciamals

see help

Description

SORTSUBSTR() sorts a delimited string of values and returns the result in a string called Output_String. Direction is case sensitive.


Examples

These examples sort "words" in a string. Note that a "word" can be defined by any delimiter.

dim string as C
dim sorted as C
dim numberstring as C
dim sortednumbers as C

string = "cde, abc, xyz, aad, gef"
sorted = sortsubstr(string, ",", "A", "C")

[COLOR="#FF0000"]numberstring = "12, 23, 123, 45, 456, 16, 56"    'convert your data to comma

sortednumbers = sortsubstr(numberstring, ",", "A", "N")  'after sorted, convert to comma_to_crlf() [/COLOR]

[Ronald Anusiewicz]

Re: How do I sort a character field containing number with and without deciamals

The obvious reason is that the app was developed in 2003 and this methodology was used all over the application.
I did try val() and that didn't work. I probably should have mentioned that I need to use this sort order in a number of forms, browses, reports and scripts if possible.
I looked at sortsubstr(). I think it will work in scripts. I don't think I can use it to sort forms and browses.

[Al Buchholz]

Re: How do I sort a character field containing number with and without deciamals

The obvious reason is that the app was developed in 2003 and this methodology was used all over the application.
I did try val() and that didn't work. I probably should have mentioned that I need to use this sort order in a number of forms, browses, reports and scripts if possible.
I looked at sortsubstr(). I think it will work in scripts. I don't think I can use it to sort forms and browses.

And how doesn't val() work?

[Ronald Anusiewicz]

Re: How do I sort a character field containing number with and without deciamals

The attached browse, Transno + val(Itemno).
It starts with:

1
10
11
12
2
3

Order.JPG

[Ronald Anusiewicz]

Re: How do I sort a character field containing number with and without deciamals

I figured it out.

transno+padl(if("." $ itemno=.F.,alltrim(itemno)+".0",alltrim(itemno) ),4,"0")

order2.JPG

Thanks for your help.

[MoGrace]

Re: How do I sort a character field containing number with and without deciamals

If you use it all the time, that would be a good one to save in the expression library.