sorting character string as number

[David Higgs]

Hi,

I have Character Field in my dbf that contains mainly numbers but also has a few entries which contain letters. I would like to sort the field so that in appears in numerical order, so far all I've achieved is:

1
100
1000
2
250
2500
3
330
3300

i.e it is sorting left to right. I would like the above to appear as:

1
2
3
100
250
330
1000
2500
3300

How can I achieve this? Any help would be most appreciated.



Regards,
David

 

[Mike Lewis]

David,

When you say "sorting", can we assume you mean indexing? In general, you don't normally need to physically sort the entries in a table. Instead, create an index.

Assuming that's what you meant, one option would be to create an index on the following function:
[tt]
TRANSFORM(MyField, "999999999")[/tt]

So you would use these commands:

[tt]USE MyTable
INDEX ON TRANSFORM(MyField, "999999999") TAG MySorted[/tt]

Then, whenever you want the records to appear in the correct sort order:
[tt]
SET ORDER TO MySorted[/tt]

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[David Higgs]

Mike,
Many thanks for the quick reply. Yes I should of said "Index" instead of sorted!! I'll take a look at the TRANSFORM command and give that a try. Since obtaining VFP 9 it has rekindled my interest in VFP and although I've managed to do most things that I've wanted, I am a bit rusty!

Regards,
David

 

[Olaf Doschke]

You can also query VAL(field) as numericfield and sort by that numeric value via ORDER BY clause or create the index on VAL(field).
Changing the field type of such fields would be a good idea, too.

Bye, Olaf.

 

[Mike Lewis]

David,

Sorry. I've had second thoughts. I hadn't taken in that this is a character field. In that case, my suggesion won't help very much. It would be better to index on [tt]VAL(MyField)[/tt] - but with the proviso that, if any instances contain letters, the first letter and all subsequent characters will be ignore.

So, this might be a typical sequence:
[tt]
xxx333
1
2
3
100
123
300
4xyz
400[/tt]

I hope this makes sense.

Keep in mind that there are nearly always several ways to solve any given problem in VFP.

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[David Higgs]

Mike, Olaf,

Thank you both for your help.

As all of the numbers were Integers I used the following statement in the end:-
INDEX on INT(VAL(loco_num)) TAG NUM
which worked ok for the numeric content but not for the Alphanumeric content. As there are only approximately 20 entries in 60,000 that contain Alphanumeric I can live with that and maybe write some code to deal with it.

Regards,
David

 

[vgulielmus]

Alternative, you can use :
INDEX on PADL(ALLTRIM((loco_num),lenght_of_loco_num_field) TAG NUM

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Olaf Doschke]

If you'd like xxx333 to be sorted as 333 stripe off the letters with a CHRTRAN operation removing all letters or all non digits, but this will convert "3x3xx3" to 333, too.
It's perhaps easier to simply right align the character values then, as Vilhelm-Ion does with PADL of the trimmed value. of course then 1,300,10 sorts as 1,10,300, anything starting with a letter will sort outside of the pure numbers and "3x3xx3" would fit in somewhere in the range of 300000 to 399999, but that should be fine.

Bye, Olaf.

 

[mmerlinn]

DPGH

With only 20 out of 60,000 containing letters, I would investigate whether it is possible or not to convert them to numbers.

If there is never a possibility that more alpha entries could be added later, then addressing the issue with some code would probably work ok. However, if new alpha entries can be added, your code would need to be updated every time and could get quite messy, not to say anything about breaking should you forget the updates.

mmerlinn

http://mmerlinn.com

Poor people do not hire employees. If you soak the rich, who are you going to work for?

"We've found by experience that people who are careless and sloppy writers are usually also careless and sloppy at thinking and coding. Answering questions for careless and sloppy thinkers is not rewarding." - Eric Raymond

 

[David Higgs]

Thanks again for all the great ideas, gets me thinking a bit more. As Mike as already said there is nearly always several ways to solve any given problem in VFP. I will endeavour to try all the various suggestions which will no doubt be educational and provide a solution. I have other projects on the go and will no doubt be asking for more practical help as and when.

Regards,
David