Solved

Sorting a list of alphanumeric strings with Visual Basic

Posted on 2003-11-06
12
1,715 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Article by: Nadia
Linear search (searching each index in an array one by one) works almost everywhere but it is not optimal in many cases. Let's assume, we have a book which has 42949672960 pages. We also have a table of contents. Now we want to read the content on p…
Iteration: Iteration is repetition of a process. A student who goes to school repeats the process of going to school everyday until graduation. We go to grocery store at least once or twice a month to buy products. We repeat this process every mont…
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

706 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

18 Experts available now in Live!

Get 1:1 Help Now