Sorting String with Numbers using VB Script

0
0

How to Sort String with Numeric values using VB Script?

Below are my strings from each row from a table:

  1. “Test 1 pass dec 2”
  2. “Test 3 fail”
  3. “Test 2 pass jun 4”
  4. “Verified”
  5. “Test 10 pass”
  6. “User Accepted”

I would to like get in below order after sorting(natural order):

  1. “Test 1 pass dec 2”
  2. “Test 2 pass jun 4”
  3. “Test 3 fail”
  4. “Test 10 pass”
  5. “User Accepted”
  6. “Verified”

Ways i have tried so far,

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

The ArrayList was sorted in below order based on ASCII which I do not prefer:

  1. “Test 1 pass dec 2”
  2. “Test 10 pass”
  3. “Test 2 pass jun 4”
  4. “Test 3 fail”
  5. “User Accepted”
  6. “Verified”

I have tried this link Sort

and i have no idea how to use AppendFormat in my case.

Note:My given string either completely string or string with numbers(dynamic) so not sure how to use RecordSet or AppendFormat here as I am new to programming.

  • You must to post comments
0
0

You can have another example.

Sub Sort
Set rs = CreateObject("ADODB.Recordset")
If LCase(Arg(1)) = "n" then
With rs
.Fields.Append "SortKey", 4
.Fields.Append "Txt", 201, 5000
.Open
Do Until Inp.AtEndOfStream
Lne
= Inp.readline
SortKey
= Mid(Lne, LCase(Arg(3)), LCase(Arg(4)) - LCase(Arg(3)))
If IsNumeric(Sortkey) = False then
Set RE = new Regexp
re
.Pattern = "[^0-9\.,]"
re
.global = true
re
.ignorecase = true
Sortkey
= re.replace(Sortkey, "")
End If
If IsNumeric(Sortkey) = False then
Sortkey
= 0
ElseIf Sortkey = "" then
Sortkey
= 0
ElseIf IsNull(Sortkey) = true then
Sortkey
= 0
End If
.AddNew
.Fields("SortKey").value = CSng(SortKey)
.Fields("Txt").value = Lne
.UpDate
Loop
If LCase(Arg(2)) = "a" then SortColumn = "SortKey ASC"
If LCase(Arg(2)) = "d" then SortColumn = "SortKey DESC"
.Sort = SortColumn
Do While not .EOF
Outp
.writeline .Fields("Txt").Value
.MoveNext
Loop
End With
ElseIf LCase(Arg(1)) = "d" then
With rs
.Fields.Append "SortKey", 4
.Fields.Append "Txt", 201, 5000
.Open
Do Until Inp.AtEndOfStream
Lne
= Inp.readline
SortKey
= Mid(Lne, LCase(Arg(3)), LCase(Arg(4)) - LCase(Arg(3)))
If IsDate(Sortkey) = False then
Set RE = new Regexp
re
.Pattern = "[^0-9\\\-:]"
re
.global = true
re
.ignorecase = true
Sortkey
= re.replace(Sortkey, "")
End If
If IsDate(Sortkey) = False then
Sortkey
= 0
ElseIf Sortkey = "" then
Sortkey
= 0
ElseIf IsNull(Sortkey) = true then
Sortkey
= 0
End If
.AddNew
.Fields("SortKey").value = CDate(SortKey)
.Fields("Txt").value = Lne
.UpDate
Loop
If LCase(Arg(2)) = "a" then SortColumn = "SortKey ASC"
If LCase(Arg(2)) = "d" then SortColumn = "SortKey DESC"
.Sort = SortColumn
Do While not .EOF
Outp
.writeline .Fields("Txt").Value
.MoveNext
Loop
End With
ElseIf LCase(Arg(1)) = "t" then
With rs
.Fields.Append "SortKey", 201, 260
.Fields.Append "Txt", 201, 5000
.Open
Do Until Inp.AtEndOfStream
Lne
= Inp.readline
SortKey
= Mid(Lne, LCase(Arg(3)), LCase(Arg(4)) - LCase(Arg(3)))
.AddNew
.Fields("SortKey").value = SortKey
.Fields("Txt").value = Lne
.UpDate
Loop
If LCase(Arg(2)) = "a" then SortColumn = "SortKey ASC"
If LCase(Arg(2)) = "d" then SortColumn = "SortKey DESC"
.Sort = SortColumn
Do While not .EOF
Outp
.writeline .Fields("Txt").Value
.MoveNext
Loop
End With
ElseIf LCase(Arg(1)) = "tt" then
With rs
.Fields.Append "SortKey", 201, 260
.Fields.Append "Txt", 201, 5000
.Open
Do Until Inp.AtEndOfStream
Lne
= Inp.readline
SortKey
= Trim(Mid(Lne, LCase(Arg(3)), LCase(Arg(4)) - LCase(Arg(3))))
.AddNew
.Fields("SortKey").value = SortKey
.Fields("Txt").value = Lne
.UpDate
Loop
If LCase(Arg(2)) = "a" then SortColumn = "SortKey ASC"
If LCase(Arg(2)) = "d" then SortColumn = "SortKey DESC"
.Sort = SortColumn
Do While not .EOF
Outp
.writeline .Fields("Txt").Value
.MoveNext
Loop
End With
End If
End Sub
  • 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