# Sorting a list of alphanumeric strings with Visual Basic

Sorting a list of alphanumeric strings with Visual Basic

I have a list of alphanumeric strings that I need to sort.  The first 2 digits represent a year 2003 = 03
1998 =98
1988 = 88
And so on.  The rest of the digits begin at 01 and go up consecutively, but sometimes there is a – and additional text or numbers.  These additional numbers or text does not need to be used in the sorting but the final output still needs to show these numbers. So now I have the job of sorting these numbers into order based on earliest year then numerical through the year.  I need some help with the algorithm to do this sorting.
For example

0024
0024-T
0024-12
8814
9812
03114

Would sort to

8814
9812
0024
0024-T
0024-12
03114

I just need some help working out the algorithm; here is a sample of the jobs

0024
0024-T
0026
0026-F
0036-3
0036-E
0062
0062-R
0066-R
0118
0118-A
0140
0140-R
0156
0202
0270
0282
0306
0308
0310
03100
03108
03110
03112
0314
0316
0324
0326
0326-1
0328
0336
0342
0354
0390
0398
8814
8924
9102
9512-6
9540-C
9634-C
9710
9730-13
9730-C
9860-8
9860-R
9934-3
LVL 1
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Author Commented:
I already have the rest of the program written in Visual Basic 6 so that is where this algorithm would need to fit.
Commented:
I created an example supposing your strings are stored in an array. If this fields are stored in a database, then we can sort it more easely with an SQL-Statement.

The example. Create a new VB-App. Populate the form with one button, and one ListBox. Paste this code and run it by pressing the Button.

Option Explicit

Private Sub Command1_Click()

Dim stringarr() As String
Dim i, j As Integer
Dim strDate As String
Dim tempstring As String

' Populating the array (on a dirty manner ;-))
stringarr = Split("0024;0024-T;0024-12;8814;9812;03114", ";")

' We use a simple sort-algoritm. You can find more complicated algoritms (for ex. QuickSort)
For i = LBound(stringarr) To UBound(stringarr)
For j = LBound(stringarr) To UBound(stringarr)
' - - - Here we make the comparision. The value is transformed by the CompareValue function
If StrComp(CompareValue(stringarr(i)), CompareValue(stringarr(j)), vbTextCompare) = -1 Then
tempstring = stringarr(i)
stringarr(i) = stringarr(j)
stringarr(j) = tempstring
End If
Next
Next

' Print the sorted array to the ListBox
Me.List1.Clear
For i = LBound(stringarr) To UBound(stringarr)
Next

End Sub

Function CompareValue(str As String) As String

' We transform the two first digits to a real date year (4 digits)
CompareValue = Format(CDate("01/01/" & Left(str, 2)), "yyyy") & Mid(str, 3)

End Function

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
jtwestmo,
You would need to write your own comparison, but you can use any string sort, below is a link to a few algorithms. As for your compare, you would need to do what is known as "windowing". Pick the earliest year you expect to see in your data (looks like 1988 in your sample) to be the start of your window. If the strings are on opposite sides on the window, the one after the window should be considered lower. If both strings are on the same side of the window, you can do a normal compare.

Dim intWorkFirst As Integer
Dim intWorkSecond As Integer

intWorkFirst = CInt(Left(strFirst, 2))
intWorkSecond = CInt(Left(strSecond, 2))

If (intWorkFirst >= 88 And intWorkSecond < 88) _
Or (intWorkFirst < 88 And intWorkSecond >= 88) Then
If intWorkFirst >= 88 And intWorkSecond < 88 Then
' strFirst is lower
Else
' strSecond is lower
End If
Else
'Normal Compare
End If

http://www.freevbcode.com/ShowCode.Asp?ID=965

Dang123

Author Commented:
Hey wsteegmans, I am actually pulling the numbers from a database but can an SQL statement be written to do that complex sorting?
Commented:
It can maybe a little bit more efficiently, but these queries will work ...

Suppose, you have one table (Table1) with one field (Field1) containing the strings ...

Using MS Access:
SELECT Field1
FROM Table1
ORDER BY Format(CDate("01/01/" & Left([field1],2)),"yyyy") & Mid([Field1],3);

Using MS SQL-Server:
SELECT     Field1
FROM         Table1
ORDER BY CONVERT(nvarchar, YEAR(CONVERT(datetime, '01/01/' + LEFT(Field1, 2)))) + SUBSTRING(Field1, 3, 99)
Author Commented:
wsteegmans

I am using Access and I always get a type mismatch when trying to run that query.  but I did use the other function you gave me and it works almost perfectly.  It breaks down at 0310 the next should be 0312 but it goes to 03100 and then all the 5 digits and then back to 0312.  I think I can work it out but I thought you might like to know.
Commented:
Strange about the query ... It should work. Can you maybe send this query to me ... ?

The first example (VB-Code) does a pure String-compare, so it compare 200310, 200312 and 2003100 for example ... A string compare gives this result:

200310
2003100
200312

If you would create three files with these filenames and sort them in Windows Explorer, you get the same result.

So, I changed the CompareValue function, so it looks for the [-] character. Just try it ...

Function CompareValue(str As String) As String

' We transform the two first digits to a real date year (4 digits)
Dim intPos As Integer
Dim strSecondPart As String
Dim strThirdPart As String

intPos = InStr(1, str, "-", vbTextCompare)
If intPos = 0 Then
strSecondPart = Format(Mid(str, 3), "000000")
strThirdPart = ""
Else
strSecondPart = Format(Mid(str, 3, intPos - 3), "000000")
strThirdPart = Mid(str, intPos)
End If

CompareValue = Format(CDate("01/01/" & Left(str, 2)), "yyyy") & strSecondPart

End Function
Author Commented:
Thanks for your help that last fuinction works great.  I would be happy to send you the entire database, I took out everything but the single table with the strings in it but I don't know how to get it to you.  Is there a way to upload it?  It is only about 180K in size now.
Commented:
I still forgot something ... Replace the last line with this one

CompareValue = Format(CDate("01/01/" & Left(str, 2)), "yyyy") & strSecondPart & strThirdPart

So, even the strings after [-] will be sorted! ;-)

How to upload? I don't think it's possible here ... But, just copy/paste the SQL-Statement from your query ... that's already a good start.
Author Commented:
SELECT JOBLIST.Job_Num FROM JOBLIST ORDER BY Format(CDate("01/01/" & Left([Job_Num],2)),"yyyy") & Mid([Job_Num],3);

The Job_Num column is stored as Text.

Your function worked perfectly but it would be faster it the database did the sorting because I have to sort the numbers then I have to loop back through and output information about each job so if I could have the database do the sorting the process would speed up.
Commented:
Still two things ...

You get this Type Mismatch error because there is at least one value in your data that is not consistent. This means there is at least one value where the first 2 characters aren't numeric. Can you check this?

When we will use the above SQL-Statement, we have the same problem as before we adapted the CompareValue Function.

But, here comes the beauty of Access, in Access you can use methods/functions in your SQL-Statement. So create a new module (or use an exising when using already modules) and paste the CompareValue function like this:

Public Function CompareValue(str As String) As String

' We transform the two first digits to a real date year (4 digits)
Dim intPos As Integer
Dim strSecondPart As String
Dim strThirdPart As String

intPos = InStr(1, str, "-", vbTextCompare)
If intPos = 0 Then
strSecondPart = Format(Mid(str, 3), "000000")
strThirdPart = ""
Else
strSecondPart = Format(Mid(str, 3, intPos - 3), "000000")
strThirdPart = Mid(str, intPos)
End If

CompareValue = Format(CDate("01/01/" & Left(str, 2)), "yyyy") & strSecondPart & strThirdPart

End Function

Then, create a query with this SQL-Statement:
SELECT JOBLIST.Job_Num
FROM JOBLIST
ORDER BY CompareValue(JOb_Num);

Your output will be sorted like our previous example with pure VB-Code.
But don't forget to check your data first. You can also adapt the Function with some error-handling ... ?
Author Commented:
You are exactly right.  I found one that had a mistake in it and when I fixed it the query ran correctly,  but like you said still have the small sort problem.  I can easly put that into Access and have it come out.  Thanks so much for your help.  I am still a little new at VB so your help is really good.  Thanks for all the info
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.