Natural Sorting using VB script

0
0

I want to sort below items using natural sorting:

“Z1″,”Z3″,”Z2″,”Z20″,”Z10”

After sorting, I am expecting the order below:

“Z1″,”Z2″,”Z3″,”Z10″,”Z20”

When I tried using array list,

Set oAlist=CreateObject("System.Collections.ArrayList")
oAlist
.sort

I am getting a sorting result based on ASCII:

Z1,Z10,Z2,Z20,Z3

Please help me on how to do this natural sorting using vb script

  • You must to post comments
0
0

For theory see here (follow the links!). Practical demo

recordset:

Option Explicit
Const adInteger = 3 ' 00000003
Const adVarChar = 200 ' 000000C8
Dim sInp : sInp = "Z1,Z3,Z2,Z20,Z10"
Dim aInp : aInp = Split(sInp, ",")
WScript
.Echo "A:", Join(aInp)
Dim oRS : Set oRS = CreateObject("ADODB.Recordset")
oRS
.Fields.Append "Word", adVarChar, 50
oRS
.Fields.Append "Length", adInteger
oRS
.Open
Dim sWord
For Each sWord In aInp
oRS
.AddNew
oRS
.Fields("Word").value = Left(sWord, 1)
oRS
.Fields("Length").value = CInt(Mid(sWord, 2))
oRS
.UpDate
Next
oRS
.Sort = "Word, Length"
Dim aTable : aTable = oRS.GetRows()
ReDim aOut(UBound(aTable, 2))
Dim i
For i = 0 To UBound(aOut)
aOut
(i) = aTable(0, i) & aTable(1, i)
Next
WScript
.Echo "B:", Join(aOut)

ArrayList

Option Explicit
Dim sInp : sInp = "Z1,Z3,Z2,Z20,Z10,E1,D3,C2,B20,A10"
Dim aInp : aInp = Split(sInp, ",")
WScript
.Echo "A:", Join(aInp)
Dim oNAL : Set oNAL = CreateObject( "System.Collections.ArrayList" )
Dim oSB : Set oSB = CreateObject( "System.Text.StringBuilder" )
Dim sWord
For Each sWord In aInp
oSB
.AppendFormat_2 "{0}{1,4}", Left(sWord, 1), CInt(Mid(sWord, 2))
sWord
= oSB.ToString()
oSB
.Length = 0
oNAL
.Add sWord
Next
oNAL
.Sort
ReDim aOut(oNAL.Count - 1)
Dim i
For i = 0 To UBound(aOut)
aOut
(i) = Left(oNAL(i), 1) & CInt(Mid(oNAL(i), 2))
Next
WScript
.Echo "B:", Join(aOut)
  • You must to post comments
Showing 1 result
Your Answer
Post as a guest by filling out the fields below or if you already have an account.
Name*
E-mail*
Website