Solved

# Sort alpha numeric

Posted on 2006-05-10
Medium Priority
367 Views
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
Question by:whiwex
• 3
• 2

LVL 16

Expert Comment

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

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

LVL 16

Expert Comment

ID: 16655017
ok,

I will check then

David
0

LVL 16

Expert Comment

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

David
0

LVL 16

Accepted Solution

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

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

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.
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
###### Suggested Courses
Course of the Month13 days, 9 hours left to enroll