?
Solved

Sort alpha numeric

Posted on 2006-05-10
8
Medium Priority
?
367 Views
Last Modified: 2012-06-21
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?
0
Comment
Question by:whiwex
  • 3
  • 2
6 Comments
 
LVL 16

Expert Comment

by:suprapto45
ID: 16654598
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
 

Author Comment

by:whiwex
ID: 16654810
David
No order by doesn't work
0
 
LVL 16

Expert Comment

by:suprapto45
ID: 16655017
ok,

I will check then

David
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 16

Expert Comment

by:suprapto45
ID: 16655032
Can you give me your sample of data, your query and the result?

David
0
 
LVL 16

Accepted Solution

by:
JohnBPrice earned 2000 total points
ID: 16667827
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
 
LVL 16

Expert Comment

by:JohnBPrice
ID: 16667901
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
Article by: evilrix
Looking for a way to avoid searching through large data sets for data that doesn't exist? A Bloom Filter might be what you need. This data structure is a probabilistic filter that allows you to avoid unnecessary searches when you know the data defin…
Starting up a Project
Screencast - Getting to Know the Pipeline

750 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