Solved

Sorting a list of alphanumeric strings with Visual Basic

Posted on 2003-11-06
12
1,725 Views
Last Modified: 2008-02-01
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
Comment
Question by:jtwestmo
  • 6
  • 5
12 Comments
 
LVL 1

Author Comment

by:jtwestmo
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

by:
wsteegmans earned 250 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)
    Me.List1.AddItem (stringarr(i))
  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

I added some comments in the code ...
0
 
LVL 9

Expert Comment

by:Dang123
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

by:jtwestmo
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

by:wsteegmans
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

by:jtwestmo
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 7

Expert Comment

by:wsteegmans
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

by:jtwestmo
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

by:wsteegmans
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

by:jtwestmo
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

by:wsteegmans
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

by:jtwestmo
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

919 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now