Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Sorting a list of alphanumeric strings with Visual Basic

Posted on 2003-11-06
Medium Priority
1,941 Views
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
0
Question by:jtwestmo
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 6
• 5

LVL 1

Author Comment

ID: 9696719
I already have the rest of the program written in Visual Basic 6 so that is where this algorithm would need to fit.
0

LVL 7

Accepted Solution

wsteegmans earned 1000 total points
ID: 9697099
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

0

LVL 9

Expert Comment

ID: 9697120
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

0

LVL 1

Author Comment

ID: 9697218
Hey wsteegmans, I am actually pulling the numbers from a database but can an SQL statement be written to do that complex sorting?
0

LVL 7

Expert Comment

ID: 9697560
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)
0

LVL 1

Author Comment

ID: 9701548
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.
0

LVL 7

Expert Comment

ID: 9701981
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
0

LVL 1

Author Comment

ID: 9702106
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.
0

LVL 7

Expert Comment

ID: 9702160
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.
0

LVL 1

Author Comment

ID: 9702308
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.
0

LVL 7

Expert Comment

ID: 9702483
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 ... ?
0

LVL 1

Author Comment

ID: 9702555
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
0

## Featured Post

Question has a verified solution.

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

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.