Sort alpha numeric

In VB
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?
whiwexAsked:
Who is Participating?
 
JohnBPriceCommented:
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
        Next
        If InNumber Then    'ending in a number
            CompareString = CompareString + Format(Numvalue, "000000")
        End If
        ExpandNumbers = CompareString
    End Function

0
 
suprapto45Commented:
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

David
0
 
whiwexAuthor Commented:
David
No order by doesn't work
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
suprapto45Commented:
ok,

I will check then

David
0
 
suprapto45Commented:
Can you give me your sample of data, your query and the result?

David
0
 
JohnBPriceCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.