A comparison of string cleaning methods

While looking at a few methods for parsing a string I came across a method for removing unwanted characters from a string. It involves looping through a string and using the Like Operator to determine if any given character is wanted.

It goes something like this:

Function CleanString(inputString As String) As String
  Dim thisCharacter As String
  Dim i As Long

  For i = 1 To Len(inputString)
    ' grab each character in turn
    thisCharacter = Mid$(inputString, i, 1)
    If thisCharacter Like "[0-9,A-Z,a-z,"" ""]" Then
      CleanString = CleanString & thisCharacter
    End If
  Next i
End Function

I like this because it doesn't require any workarounds I usually use, like creating an array of "bad" characters and comparing each character in the input string with each "bad" character to see if there's a match. That could be a lot of looping.

I thought there might be a faster way to do the same thing. Building the cleaned string one character at a time must be (relatively) time consuming, right?

So here's what I came up with. It assumes that the input string is valid, then uses the Replace function to strip out unwanted characters. It still loops through the string — that's the only way to avoid having to specify which characters I don't want, which is everything but letters, numbers and spaces.

Function CleanString2(inputString As String) As String

  Dim i As Long
  Dim thisCharacter As String

  ' assume valid string
  CleanString2 = inputString
  i = 1
  Do While i <= Len(CleanString2)
    thisCharacter = Mid$(CleanString2, i, 1)
    If Not thisCharacter Like "[0-9,A-Z,a-z,"" ""]" Then
      CleanString2 = Replace(CleanString2, thisCharacter, "")
    Else
      i = i + 1
    End If
  Loop

End Function

A Do While Loop is required because the string changes size whenever the Replace Function is used. If we used a For Loop, eventually the loop counter would be greater than the size of the string (assuming we did some replacements) and the Mid Function would throw an error. The Len Keyword dynamically changes with the size of the string.

This should be faster, because the more bad characters there are, the less iterations, right? Let's find out!

The Test

I reused a simple procedure that runs both of the above functions and times the result.

Public Declare Function timeGetTime Lib "winmm.dll" () As Long

Sub TestStringClean()

  Dim startTimeBuildString As Long
  Dim endTimeBuildString As Long
  Dim startTimeReplaceString As Long
  Dim endTimeReplaceString As Long
  Dim i As Long
  Dim retVal As String

  Const numberofLoops As Long = 100000
  Const stringtobefixed As String = "abcdefgeuhuep9Y*&#PO#M@##}{{:><?><}"

  startTimeBuildString = timeGetTime
  For i = 1 To numberofLoops
    retVal = CleanString(stringtobefixed)
  Next i
  endTimeBuildString = timeGetTime

  startTimeReplaceString = timeGetTime
  For i = 1 To numberofLoops
    retVal = CleanString2(stringtobefixed)
  Next i
  endTimeReplaceString = timeGetTime

  MsgBox "Time using Build String Method: " & ((endTimeBuildString - startTimeBuildString) / 1000) & vbNewLine & _
    "Time using Replace Method: " & ((endTimeReplaceString - startTimeReplaceString) / 1000)
  
End Sub

The Results

My assumption is that my method will be faster. Let's start at 10,000 iterations.

Huh? Looks like the method I found is faster than the one I wrote. Let's try 100,000 iterations.

Clearly the "build-as-you-go" method is faster. Just for kicks let's try 1,000,000 iterations, but at this point I assume my method will be slower.

Conclusion

My method was considerably slower. I still like it as an example for checking for negative conditions, but in general I'm going to stick with the method I found somewhere online. Maybe it would be faster if rewritten to build the string instead of using the Replace Function?