[AccessD] Use Regex - Create Camel Case

max.wanadoo at gmail.com max.wanadoo at gmail.com
Mon Oct 1 05:34:00 CDT 2007


Hi Gustav,
I have tried your version of poking the values into a string using the MID.
The results is 31 seconds LONGER than the previous verions.
Both version are below.  Identical (I think) apart from the poking bit.

Max

Option Compare Database
Option Explicit
Const conBadChars As String = "!£$%^&*()_-+@'#~?><|\, " ' space also in this
string
Const conIterations As Long = 1000000 ' one million iterations

Function CamelCaseBestSoFar4VBAPoking()
  ' This times at 4 mins 16 seconds
  Dim tStartTime As Date, tEndTime As Date, tLapsedTime As Date, iLoop As
Long, iVarLoop As Integer
  Dim iLenLoop As Integer, iVars As Integer
  Dim bFlipCase As Boolean, str2Parse As String, strResult As String, strBit
As String
  Dim varStr(5), lngPos As Long
  varStr(1) = "John colby  " ' Result wanted: "JohnColby"
  varStr(2) = "%idiotic_Field*name&!@" ' Result wanted: "IdioticFieldName"
  varStr(3) = " # hey#hey#Hey,hello_world$%#" ' Result wanted:
"HeyHeyHeyHelloWorld"
  varStr(4) = "@#$this#is_a_test_of_the-emerGency-broadcast-system" ' Result
wanted: "ThisIsATestOftheEmergencyBroadcastSystem"
  varStr(5) = "thisisastringwithnobadchars" ' Result wanted:
"Thisisastringwithnobadchars"
  iVars = 5
  tStartTime = Now
  For iLoop = 1 To conIterations
    For iVarLoop = 1 To iVars
      str2Parse = varStr(iVarLoop): bFlipCase = True: strResult =
Left(Space(255), Len(str2Parse)): lngPos = 0
      For iLenLoop = 1 To Len(str2Parse)
        strBit = LCase(Mid(str2Parse, iLenLoop, 1))
        If InStr(conBadChars, strBit) = 0 Then
          lngPos = lngPos + 1
          If bFlipCase = True Then strBit = UCase(strBit): bFlipCase = False
          Mid(strResult, lngPos, 1) = strBit
        Else
          bFlipCase = True
        End If
      Next iLenLoop
      strResult = Trim(strResult) ' drop any spaces left in string
      'Debug.Print strResult
    Next iVarLoop
  Next iLoop
  tEndTime = Now
  tLapsedTime = tEndTime - tStartTime
  'MsgBox tLapsedTime:
  Debug.Print tLapsedTime
End Function

Function CamelCaseBestSoFar4VBA()
  ' This times at 3 mins 45 seconds
  Dim tStartTime As Date, tEndTime As Date, tLapsedTime As Date, iLoop As
Long, iVarLoop As Integer
  Dim iLenLoop As Integer, iVars As Integer
  Dim bFlipCase As Boolean, str2Parse As String, strResult As String, strBit
As String
  Dim varStr(5)
  varStr(1) = "John colby  " ' Result wanted: "JohnColby"
  varStr(2) = "%idiotic_Field*name&!@" ' Result wanted: "IdioticFieldName"
  varStr(3) = " # hey#hey#Hey,hello_world$%#" ' Result wanted:
"HeyHeyHeyHelloWorld"
  varStr(4) = "@#$this#is_a_test_of_the-emerGency-broadcast-system" ' Result
wanted: "ThisIsATestOftheEmergencyBroadcastSystem"
  varStr(5) = "thisisastringwithnobadchars" ' Result wanted:
"Thisisastringwithnobadchars"
  iVars = 5
  tStartTime = Now
  For iLoop = 1 To conIterations
    For iVarLoop = 1 To iVars
      str2Parse = LCase(varStr(iVarLoop)): bFlipCase = True: strResult = ""
      For iLenLoop = 1 To Len(str2Parse)
        strBit = Mid(str2Parse, iLenLoop, 1)
        If InStr(conBadChars, strBit) = 0 Then
          If bFlipCase = True Then strBit = UCase(strBit): bFlipCase = False
          strResult = strResult & strBit
        Else
          bFlipCase = True
        End If
      Next iLenLoop
      'Debug.Print strResult
    Next iVarLoop
  Next iLoop
  tEndTime = Now
  tLapsedTime = tEndTime - tStartTime
  'MsgBox tLapsedTime:
  Debug.Print tLapsedTime
End Function 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Monday, October 01, 2007 9:57 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Use Regex - Create Camel Case

Hi Max and Shamil

Max, the main reason for VBA running slow on string concatenation is this
construct:

        strResult = strResult & strBit

Indeed for long strings (some 10K) this is so slow that it is hard to
believe.
The traditional work-around is to create a dummy target string and then
replace the chars one by one using Mid().
Here's an example (though path/file names seldom are so long that it
matters):

Public Function TrimFileName( _
  ByVal strFileName As String) _
  As String

' Replaces characters in strFileName that are ' not allowed by Windows as a
file name.
' Truncates length of strFileName to clngFileNameLen.
'
' 2000-12-07. Gustav Brock, Cactus Data ApS, Copenhagen ' 2002-05-22.
Replaced string concatenating with Mid().

  ' No special error handling.
  On Error Resume Next

  ' String containing all not allowed characters.
  Const cstrInValidChars  As String = "\/:*?""<>|"
  ' Replace character for not allowed characters.
  Const cstrReplaceChar   As String * 1 = "-"
  ' Maximum length of a file name.
  Const clngFileNameLen  As Long = 255
  
  Dim lngLen    As Long
  Dim lngPos    As Long
  Dim strChar   As String
  Dim strTrim   As String
  
  ' Strip leading and trailing spaces.
  strTrim = Left(Trim(strFileName), clngFileNameLen)
  lngLen = Len(strTrim)
  For lngPos = 1 To lngLen Step 1
    strChar = Mid(strTrim, lngPos, 1)
    If InStr(cstrInValidChars, strChar) > 0 Then
      Mid(strTrim, lngPos) = cstrReplaceChar
    End If
  Next
    
  TrimFileName = strTrim

End Function

Shamil, I have not been working with this in C# but I think you are on the
right track using arrays. I hope to find some time to experiment with your
code examples. 
As we all know, for validation of a user input in a textbox, speed is of
zero practical importance, but from time to time your task is to manipulate
not one but thousands of strings and then it matters.

/gustav

>>> max.wanadoo at gmail.com 30-09-2007 10:52 >>>
Hi Shamil,
Clearly your compiled solution is by way and far the quickest solution.
I have tried all sorts of VBA solutions including looking at XOR, IMP, EQV,
bitwise solutions, but there overheads were considerable.
The best I can come up with in VBA is below.
One million iterations on my Dell Inspiron comes in at 3 min 52 secs.

If John didn't want to Hump it, then RegExpr appears to be the answer within
pure VBA 

Max

Function dbc2()
  Const conGoodChars As String = "abcdefghijklmnopqrstuvwxyz" ' valid
characters
  Const conBadChars As String = "£$%^&*()_-+@'#~?><|\, " ' space also in
this string
  Const conLoops As Long = 1000000
  Dim tStartTime As Date, tEndTime As Date, tLapsedTime As Date, iLoop As
Long, iVars As Integer, iVarLoop As Integer
  Dim iLen As Integer, strTemp As String, bFlipCase As Boolean, str2Parse As
String, strResult As String, strBit As String
  Dim varStr(5)
  varStr(1) = "John colby  "
  varStr(2) = "%idiotic_Field*name&!@"
  varStr(3) = " # hey#hey#Hey,hello_world$%#"
  varStr(4) = "@#$this#is_a_test_of_the-emerGency-broadcast-system"
  varStr(5) = "thisisastringwithnobadchars"
  iVars = 5
  tStartTime = Now
  For iLoop = 1 To conLoops
    For iVarLoop = 1 To iVars
      strResult = ""
      str2Parse = LCase(varStr(iVarLoop))
      str2Parse = UCase(Left(str2Parse, 1)) & Mid(str2Parse, 2)
      For iLen = 1 To Len(str2Parse)
        strBit = Mid(str2Parse, iLen, 1)
        If InStr(conBadChars, strBit) = 0 Then
          If bFlipCase = True Then strBit = UCase(strBit): bFlipCase = False
          strResult = strResult & strBit
        Else
          bFlipCase = True
        End If
      Next iLen
      'Debug.Print strResult
    Next iVarLoop
  Next iLoop
  tEndTime = Now
  tLapsedTime = tEndTime - tStartTime
  MsgBox tLapsedTime: Debug.Print tLapsedTime End Function
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil
Salakhetdinov
Sent: Sunday, September 30, 2007 9:01 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Use Regex - Create Camel Case

<<<
However for more complicated
string operations like validating an email address, a regex would be very
suitable and doable in one line vs. many, many lines the other way.
>>>
Hi Mike,

That's clear, and the John's task is to get the speediest solution.


--
Shamil
 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Michael Bahr
Sent: Sunday, September 30, 2007 6:42 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Use Regex - Create Camel Case

Hi Shamil, yes regex's are slower in .Net due to I believe all the objects
overhead.  For simple string operations regexes would probrably not be
effiecent BUT would be easier to write.  However for more complicated string
operations like validating an email address, a regex would be very suitable
and doable in one line vs. many, many lines the other way.

Mike...

> Hi All,
>
> I wanted to note: I have seen somewhere an article about RegEx being 
> considerably slower than a mere strings comparison etc. I cannot find 
> this article now, can you?
>
> Here is a similar article on ColdFusion and Java (watch line wraps) -
>
>
http://www.bennadel.com/blog/410-Regular-Expression-Finds-vs-String-Finds.ht

> m
>
> The info above should be also true for C#/VB.NET (just remember there 
> are no miracles in this world)...
>
> John, this could be critical information for you because of your 
> computers processing zillion gigabytes of data - if that slowness of 
> RegEx
vs.
> string
> comparison operation proves to be true then mere chars/strings 
> comparison and simple iteration over source string's char array could 
> be the most effective solution, which will save you hours and hours of
computing time:
>
> - define a 256 bytes long table (I guess you use extended ASCII (256 
> chars
> max) only John - right?) with to be stripped out chars marked by 1;
> - define upperCase flag;
> - allocate destination string, which is as long as the source one - 
> use StringBuilder;
> - iterate source string and use current char's ASCII code as an index 
> of a cell of array mentioned above:
>     a) if the array's cell has value > 0  then the source char should 
> be stripped out/skipped; set uppercase flag = true;
>     b) if the array's cell has zero value and uppercase flag = true 
> then uppercase current source char and copy it to the destination 
> StringBuilder's; set uppercase flag = false;
>     c) if the array's cell has zero value and uppercase flag = false 
> then lower case current source char and copy it to the destination 
> StringBuilder's string;
>
>
> Here is C# code:
>
>
> private static string[] delimiters = "
> |%|*|$|@|!|#|&|^|_|-|,|.|;|:|(|)".Split('|');
> private static byte[] sieve = new byte[255]; private static bool 
> initialized = false; static void JamOutBadChars() { if (!initialized) 
> {
>     sieve.Initialize();
>     foreach (string delimiter in delimiters)
>     {
>         sieve[(int)delimiter.Substring(0, 1).ToCharArray()[0]] = 1;
>     }
>     initialized = true;
> }
> string[] test = {"John colby  ",
>         "%idiotic_Field*name&!@",
>         " # hey#hey#Hey,hello_world$%#",
>         "@#$this#is_a_test_of_the-emergency-broadcast-system          "};
>
> foreach (string source in test)
> {
>     StringBuilder result = new StringBuilder(source.Length);
>     bool upperCase = true;
>     foreach (char c in source.ToCharArray())
>     {
>         if (sieve[(int)c] > 0) upperCase = true;
>         else if (upperCase)
>         {
>             result.Append(c.ToString().ToUpper());
>             upperCase = false;
>         }
>         else result.Append(c.ToString().ToLower());
>     }
>     Console.WriteLine(source + " => {" + result + "}"); } }
>
> --
> Shamil
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Michael 
> Bahr
> Sent: Friday, September 28, 2007 10:25 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Use Regex - Create Camel Case
>
> Hi John, here is one way to do it (although there are many ways to get 
> the same end result).  Mind you this is air code but hopefully should 
> be enough to get you going.  You will need to create the main loop 
> within your code.
>
> Create a list of all delimiters that are used in your CSV files such 
> as delimiters = '%|*|$|@|!|#|&|^|_|-|,|.|;|:| '
>
> then run through your CSV files line by line evaluating the line 
> saving the line into an array thisarray = Split(line, delimiters)
>
> then run through the array performing a Ucase on the first letter of 
> each word newline = ""
> For item=1 to ubound
>   newline = newline & whatEverToCapFirstChar(item) Next item
>
> where ubound is the array size
>
>
> Now here are two scripts that do the same thing, one is Perl and the 
> other is TCL.  Both of these languages are open source and free and 
> can be gotten at http://www.activestate.com/Products/languages.plex
>
> Perl:
>
> my $delimiters = '/:| |\%|\*|\$|\@|\!|\#|\&|^|_|-|,|\./';
> my @test = ("John colby",
>             "%idiotic_Field*name",
>             "hey#hey#Hey,hello_world",
>             "this#is_a_test_of_the-emergency-broadcast-system");
>
> foreach my $item (@test) {
>    my $temp = "";
>    my @list = split ($delimiters, $item);
>    foreach my $thing (@list) {
>       $temp .= ucfirst($thing);
>    }
>    print "$temp\n";
>
> }
>
> Result
> d:\Perl>pascalcase.pl
> JohnColby
> IdioticFieldName
> HeyHeyHeyHelloWorld
> ThisIsATestOfTheEmergencyBroadcastSystem
>
> TCL:
>
> set delimiters {%|*|$|@|!|#|&|^|_|-|,|.|;|:|\ "} set test [list {John 
> colby} {%idiotic_Field*name} {hey#hey#Hey,hello_world} 
> {this#is_a_test_of_the-emergency-broadcast-system}]
>
>
> foreach item $test {
>    set str ""
>    set mylist [split $item, $delimiters]
>    foreach thing $mylist {
>       set s [string totitle $thing]
>       set str "$str$s"
>    }
>    puts $str
>
> }
>
> Results
> D:\VisualTcl\Projects>tclsh pascalcase.tcl JohnColby IdioticFieldName 
> HeyHeyHeyHelloWorld ThisIsATestOfTheEmergencyBroadcastSystem
>
>
> hth, Mike...

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com





More information about the AccessD mailing list