sorting alpha-numeric dos foxpro records

Posted on 2006-05-24
Last Modified: 2013-11-12

How can dos foxpro records be sorted alphanumerically so that record a10 does not list before record a3?  i.e  a3, a10   Thanks, ch
Question by:telep
    LVL 6

    Assisted Solution

    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
    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

    So all together select

    LVL 6

    Assisted Solution

    Based on this doc

    LEFT appears to be a valid FoxPro function
    LPAD is the STUFF function
    and LENGTH is LEN
    LVL 31

    Assisted Solution

    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.
    LVL 18

    Accepted Solution


    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.


    Author Comment

    Thank you all!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Training Course: Java/J2EE and SOA

    This course will cover both core and advanced Java concepts like Database connectivity, Threads, Exception Handling, Collections, JSP, Servlets, XMLHandling, and more. You'll also learn various Java frameworks like Hibernate and Spring.

    Suggested Solutions

    What is Waterfall Model? Waterfall model is the classic Software Development Life Cycle method practiced in software development process. As the name "waterfall" describes, this development is flowing downwards steadily like waterfall, i.e., procee…
    Introduction Many of the most common information processing tasks require sorting data sets.  For example, you may want to find the largest or smallest value in a collection.  Or you may want to order the data set in numeric or alphabetical order. …
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now