• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 521
  • Last Modified:

sorting alpha-numeric dos foxpro records

Hi,

How can dos foxpro records be sorted alphanumerically so that record a10 does not list before record a3?  i.e  a3, a10   Thanks, ch
0
telep
Asked:
telep
4 Solutions
 
itdrmsCommented:
Since no one's answered you in a while -- trying to see if I can help a bit...
Don't know FoxPro -- but you'll have to use string functions to insert the right amount of 0's.
Look up string functions in your help for exact syntax.
You want to strip off the first alpha -- assuming there is only one, you want to do a function like
LEFT(fieldname,1)
then concatenate it to the max padded numeric part  (concatenation symbol might be &, + or ||)
Assuming your numeric portion goes up to 4 digits, if you have a pad function:
LPAD([numeric portion of field],'0',4)
And to get that numeric portion
SUBSTRING(fieldname,2,length(fieldname)-1)

So all together select
LEFT(fieldname,1)+LPAD(SUBSTRING(fieldname,2,length(fieldname)-1),'0',4)

0
 
itdrmsCommented:
Based on this doc
http://portal.dfpug.de/dfpug/Dokumente/Partner/Toolkit/Strings/Strings.doc

LEFT appears to be a valid FoxPro function
LPAD is the STUFF function
SUBSTRING is SUBSTR
and LENGTH is LEN
0
 
moorhouselondonCommented:
Gosh, is this still open?

How many numerics are in the field?  If you have ABC123ABC is this the "worst case scenario"?  I would be inclined to split this into three fields

Field1 contains the first alpha chars
Field2 contains the numerics in the middle
Field3 contains any trailing alpha chars

and do a compound index, with Field2 being numeric, should sort numerically.

Alternatively, (as has already been implied) stuff leading zeros onto the numeric element - the number of leading zeros being dictated by the max number of digits you are expecting here e.g., ABC000000123ABC in my example.  

Either way would work.
0
 
Jose ParrotGraphics ExpertCommented:
Hi,

Take a look at previous solved question
Title: Sort a string
asked by tgrambo on 06/12/2000 07:13PM BRT  

It may be useful.

Jose
0
 
telepAuthor Commented:
Thank you all!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now