Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2019
  • Last Modified:

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
0
jtwestmo
Asked:
jtwestmo
  • 6
  • 5
1 Solution
 
jtwestmoAuthor Commented:
I already have the rest of the program written in Visual Basic 6 so that is where this algorithm would need to fit.
0
 
wsteegmansCommented:
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
 
Dang123Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
jtwestmoAuthor Commented:
Hey wsteegmans, I am actually pulling the numbers from a database but can an SQL statement be written to do that complex sorting?
0
 
wsteegmansCommented:
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
 
jtwestmoAuthor 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.
0
 
wsteegmansCommented:
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
 
jtwestmoAuthor 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.
0
 
wsteegmansCommented:
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
 
jtwestmoAuthor 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.  
0
 
wsteegmansCommented:
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
 
jtwestmoAuthor 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
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now