Sort alpha numeric

Posted on 2006-05-10
Last Modified: 2012-06-21
Does anyone have a peice of code that will sort strings that contain numbers.
I'm querying a access database and sorting a field that is alpha numeric. I move the records to a mshflexgrid.
I am willing to sort the flexgrid if needed?
Question by:whiwex
    LVL 16

    Expert Comment

    Can't you just use ORDER BY? I thought that the ORDER BY has some support for String as well. Give it a try and let me know if it does not work


    Author Comment

    No order by doesn't work
    LVL 16

    Expert Comment


    I will check then

    LVL 16

    Expert Comment

    Can you give me your sample of data, your query and the result?

    LVL 16

    Accepted Solution

    You mean you want embedded numbers treated as numbers, e.g.

    Test_5_Stuff comes BEFORE Test_15_Stuff, even though a straight alpha sort would put Test_15_Stuff first.

    There is no builtin function I know of that does that, one way is to write your own comparison function, another is to zero fill all numbers within the string, like this

        Function ExpandNumbers(ByVal TestString As String) As String
            'convert all numbers in each to 6 digit numbers and then compare alpha
            Dim i As Integer
            Dim InNumber As Boolean
            Dim Numvalue As Integer
            Dim CompareString As String
            For i = 1 To Len(TestString)
                If IsNumeric(Mid(TestString, i, 1)) Then
                    Numvalue = Numvalue * 10 + CInt(Mid(TestString, i, 1))
                    InNumber = True
                Else    'alpha character
                    If InNumber Then    'we were building a number, append it
                        CompareString = CompareString + Format(Numvalue, "000000")
                        InNumber = False
                        Numvalue = 0
                    End If
                    'now append the alpha character
                    CompareString = CompareString + Mid(TestString, i, 1)
                End If
            If InNumber Then    'ending in a number
                CompareString = CompareString + Format(Numvalue, "000000")
            End If
            ExpandNumbers = CompareString
        End Function

    LVL 16

    Expert Comment

    An access query would then look like

    SELECT Table1.field1, ExpandNumbers([Table1]![field1]) AS Expr1
    FROM Table1
    ORDER BY ExpandNumbers([Table1]![field1]);

    and the results would look like

    field1                      Expr1
    Test_5_Stuff      Test_000005_Stuff
    Test_15_Stufff      Test_000015_Stufff

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    sumHeights  challenge 17 50
    mergeTwo  challenge 13 53
    Sorting in Excel with Group Headers if the Exist 2 53
    Math homework question 5 43
    The greatest common divisor (gcd) of two positive integers is their largest common divisor. Let's consider two numbers 12 and 20. The divisors of 12 are 1, 2, 3, 4, 6, 12 The divisors of 20 are 1, 2, 4, 5, 10 20 The highest number among the c…
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
    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…

    733 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

    22 Experts available now in Live!

    Get 1:1 Help Now