Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Optimized way to transfer from String to StringBuilder
Message
From
05/02/2014 13:36:50
Michel Fournier
Level Extreme Inc.
Petit-Rocher, New Brunswick, Canada
 
 
To
05/02/2014 13:32:02
Viv Phillips
Hay-On-Wye, United Kingdom
General information
Forum:
ASP.NET
Category:
Coding, syntax and commands
Title:
Re: Optimized way to transfer from String to StringBuilder
Environment versions
Environment:
VB 9.0
OS:
Windows 7
Network:
Windows 2003 Server
Database:
MS SQL Server
Application:
Web
Miscellaneous
Thread ID:
01593079
Message ID:
01593312
Views:
22
>Don't understand. What doesn't my StringBuilder() do that requires more work ?

This is the actual method:
    ' Parse a string for SQL Server
    ' expC1 String
    Public Function ParseForSQLServer(ByVal tcString As String) As String
        Dim laReservedWord(28) As Object
        Dim laReservedWordForFieldOnly(1) As Object
        Dim lnCounter As Integer = 0
        Dim lcString As String = ""
        Dim lcWord As String = ""

        ' Define the reserved words
        laReservedWord(1) = "Order"
        laReservedWord(2) = "All"
        laReservedWord(3) = "Table"
        laReservedWord(4) = "From"
        laReservedWord(5) = "To"
        laReservedWord(6) = "Delete"
        laReservedWord(7) = "Insert"
        laReservedWord(8) = "Default"
        laReservedWord(9) = "End"
        laReservedWord(10) = "Index"
        laReservedWord(11) = "File"
        laReservedWord(12) = "Current"
        laReservedWord(13) = "Expand"
        laReservedWord(14) = "Image"
        laReservedWord(15) = "Read"
        laReservedWord(16) = "View"
        laReservedWord(17) = "Desc"
        laReservedWord(18) = "Update"
        laReservedWord(19) = "User"
        laReservedWord(20) = "In"
        laReservedWord(21) = "Public"
        laReservedWord(22) = "Unique"
        laReservedWord(23) = "Read"
        laReservedWord(24) = "Site"
        laReservedWord(25) = "Full"
        laReservedWord(26) = "Key"
        laReservedWord(27) = "Group"
        laReservedWord(28) = "Like"

        ' Define the reserved words for fields only
        laReservedWordForFieldOnly(1) = "As"

        lcString = tcString

        ' Make sure we have a value
        If lcString.Length = 0 Then
            Return ""
        End If

        ' Add a space at the end to make sure to include the last word
        lcString = lcString + " "

        ' Scan all reserved words
        For lnCounter = 1 To 28
            lcWord = laReservedWord(lnCounter)

            ' Field
            lcString = oApp.StrTran(lcString, "," + lcWord + ".", ",[" + lcWord + "].")
            lcString = oApp.StrTran(lcString, "." + lcWord + ",", ".[" + lcWord + "],")
            lcString = oApp.StrTran(lcString, "." + lcWord + " ", ".[" + lcWord + "] ")
            lcString = oApp.StrTran(lcString, " " + lcWord + ",", " [" + lcWord + "],")
            lcString = oApp.StrTran(lcString, "," + lcWord + ")", ",[" + lcWord + "])")

            ' INNER JOIN or AS clause in the fields
            lcString = oApp.StrTran(lcString, " " + lcWord + " ", " [" + lcWord + "] ")
            lcString = oApp.StrTran(lcString, " " + lcWord + ".", " [" + lcWord + "].")
            lcString = oApp.StrTran(lcString, "." + lcWord + "=", ".[" + lcWord + "]=")
            lcString = oApp.StrTran(lcString, "=" + lcWord + ".", "=[" + lcWord + "].")
            lcString = oApp.StrTran(lcString, "." + lcWord + " ", ".[" + lcWord + "] ")

            ' Where
            lcString = oApp.StrTran(lcString, "(" + lcWord + ".", "([" + lcWord + "].")
            lcString = oApp.StrTran(lcString, "." + lcWord + ")", ".[" + lcWord + "])")
            lcString = oApp.StrTran(lcString, "." + lcWord + ">", ".[" + lcWord + "]>")
            lcString = oApp.StrTran(lcString, "." + lcWord + "<", ".[" + lcWord + "]<")
            lcString = oApp.StrTran(lcString, "." + lcWord + "=", ".[" + lcWord + "]=")

            ' Update
            lcString = oApp.StrTran(lcString, " " + lcWord + "=", " [" + lcWord + "]=")

            ' Order
            lcString = oApp.StrTran(lcString, "," + lcWord + " ", ",[" + lcWord + "] ")
            lcString = oApp.StrTran(lcString, "," + lcWord + ",", ",[" + lcWord + "],")

            ' Check for the last occurrence
            If Mid(lcString, lcString.Length - lcWord.Length) = "." + lcWord Then
                lcString = Mid(lcString, 1, lcString.Length - lcWord.Length) + "[" + lcWord + "] "
            End If

            ' Check for the first occurrence
            If Mid(lcString, 1, lcWord.Length + 1) = lcWord + "." Then
                lcString = "[" + Mid(lcString, 1, lcWord.Length) + "]" + Mid(lcString, lcWord.Length + 1)
            End If

            ' Field in update
            lcString = oApp.StrTran(lcString, "," + lcWord + "=", ",[" + lcWord + "]=")

        Next

        ' Scan all reserved words for fields only
        For lnCounter = 1 To 1
            lcWord = laReservedWordForFieldOnly(lnCounter)

            ' Field
            lcString = oApp.StrTran(lcString, "," + lcWord + ".", ",[" + lcWord + "].")
            lcString = oApp.StrTran(lcString, "." + lcWord + ",", ".[" + lcWord + "],")
            lcString = oApp.StrTran(lcString, "." + lcWord + " ", ".[" + lcWord + "] ")
            lcString = oApp.StrTran(lcString, " " + lcWord + ",", " [" + lcWord + "],")
            lcString = oApp.StrTran(lcString, "," + lcWord + ")", ",[" + lcWord + "])")

            ' INNER JOIN or AS clause in the fields
            lcString = oApp.StrTran(lcString, " " + lcWord + ".", " [" + lcWord + "].")
            lcString = oApp.StrTran(lcString, "." + lcWord + "=", ".[" + lcWord + "]=")
            lcString = oApp.StrTran(lcString, "=" + lcWord + ".", "=[" + lcWord + "].")
            lcString = oApp.StrTran(lcString, "." + lcWord + " ", ".[" + lcWord + "] ")

            ' Where
            lcString = oApp.StrTran(lcString, "(" + lcWord + ".", "([" + lcWord + "].")
            lcString = oApp.StrTran(lcString, "." + lcWord + ")", ".[" + lcWord + "])")
            lcString = oApp.StrTran(lcString, "." + lcWord + ">", ".[" + lcWord + "]>")
            lcString = oApp.StrTran(lcString, "." + lcWord + "<", ".[" + lcWord + "]<")
            lcString = oApp.StrTran(lcString, "." + lcWord + "=", ".[" + lcWord + "]=")

            ' Update
            lcString = oApp.StrTran(lcString, " " + lcWord + "=", " [" + lcWord + "]=")

            ' Order
            lcString = oApp.StrTran(lcString, "," + lcWord + " ", ",[" + lcWord + "] ")
            lcString = oApp.StrTran(lcString, "," + lcWord + ",", ",[" + lcWord + "],")

            ' Check for the last occurrence
            If Mid(lcString, lcString.Length - lcWord.Length) = "." + lcWord Then
                lcString = Mid(lcString, 1, lcString.Length - lcWord.Length) + "[" + lcWord + "] "
            End If

            ' Check for the first occurrence
            If Mid(lcString, 1, lcWord.Length + 1) = lcWord + "." Then
                lcString = "[" + Mid(lcString, 1, lcWord.Length) + "]" + Mid(lcString, lcWord.Length + 1)
            End If

            ' Field in update
            lcString = oApp.StrTran(lcString, "," + lcWord + "=", ",[" + lcWord + "]=")

        Next

        ' Trim everything
        lcString = Trim(lcString)

        Return lcString
    End Function
This is an attempt I made with StringBuilder but it was slower:
    ' Parse a string for SQL Server
    ' expC1 String
    Public Function ParseForSQLServer(ByVal tcString As String) As String
        Dim laReservedWord(28) As Object
        Dim laReservedWordForFieldOnly(1) As Object
        Dim lnCounter As Integer = 0
        Dim lcWord As String = ""
        Dim loStringBuilder As StringBuilder = New StringBuilder

        ' Define the reserved words
        laReservedWord(1) = "Order"
        laReservedWord(2) = "All"
        laReservedWord(3) = "Table"
        laReservedWord(4) = "From"
        laReservedWord(5) = "To"
        laReservedWord(6) = "Delete"
        laReservedWord(7) = "Insert"
        laReservedWord(8) = "Default"
        laReservedWord(9) = "End"
        laReservedWord(10) = "Index"
        laReservedWord(11) = "File"
        laReservedWord(12) = "Current"
        laReservedWord(13) = "Expand"
        laReservedWord(14) = "Image"
        laReservedWord(15) = "Read"
        laReservedWord(16) = "View"
        laReservedWord(17) = "Desc"
        laReservedWord(18) = "Update"
        laReservedWord(19) = "User"
        laReservedWord(20) = "In"
        laReservedWord(21) = "Public"
        laReservedWord(22) = "Unique"
        laReservedWord(23) = "Read"
        laReservedWord(24) = "Site"
        laReservedWord(25) = "Full"
        laReservedWord(26) = "Key"
        laReservedWord(27) = "Group"
        laReservedWord(28) = "Like"

        ' Define the reserved words for fields only
        laReservedWordForFieldOnly(1) = "As"

        loStringBuilder.Append(tcString)

        ' Make sure we have a value
        If loStringBuilder.Length = 0 Then
            Return ""
        End If

        ' Add a space at the end to make sure to include the last word
        loStringBuilder.Append(" ")

        ' For each reserved word
        For lnCounter = 1 To 28
            lcWord = laReservedWord(lnCounter)

            ' Field
            loStringBuilder.Replace("," + lcWord + ".", ",[" + lcWord + "].")
            loStringBuilder.Replace("," + lcWord + ".", ",[" + lcWord + "].")
            loStringBuilder.Replace("." + lcWord + ",", ".[" + lcWord + "],")
            loStringBuilder.Replace("." + lcWord + " ", ".[" + lcWord + "] ")
            loStringBuilder.Replace(" " + lcWord + ",", " [" + lcWord + "],")
            loStringBuilder.Replace("," + lcWord + ")", ",[" + lcWord + "])")

            ' INNER JOIN or AS clause in the fields
            loStringBuilder.Replace(" " + lcWord + " ", " [" + lcWord + "] ")
            loStringBuilder.Replace(" " + lcWord + ".", " [" + lcWord + "].")
            loStringBuilder.Replace("." + lcWord + "=", ".[" + lcWord + "]=")
            loStringBuilder.Replace("=" + lcWord + ".", "=[" + lcWord + "].")
            loStringBuilder.Replace("." + lcWord + " ", ".[" + lcWord + "] ")

            ' Where
            loStringBuilder.Replace("(" + lcWord + ".", "([" + lcWord + "].")
            loStringBuilder.Replace("." + lcWord + ")", ".[" + lcWord + "])")
            loStringBuilder.Replace("." + lcWord + ">", ".[" + lcWord + "]>")
            loStringBuilder.Replace("." + lcWord + "<", ".[" + lcWord + "]<")
            loStringBuilder.Replace("." + lcWord + "=", ".[" + lcWord + "]=")

            ' Update
            loStringBuilder.Replace(" " + lcWord + "=", " [" + lcWord + "]=")

            ' Order
            loStringBuilder.Replace("," + lcWord + " ", ",[" + lcWord + "] ")
            loStringBuilder.Replace("," + lcWord + ",", ",[" + lcWord + "],")

            ' Check for the last occurrence
            If Mid(loStringBuilder.ToString, loStringBuilder.Length - lcWord.Length) = "." + lcWord Then
                loStringBuilder.Remove(loStringBuilder.Length - 1 - lcWord.Length, lcWord.Length)
                loStringBuilder.Append("[" + lcWord + "] ")
            End If

            ' Check for the first occurrence
            If Mid(loStringBuilder.ToString, 1, lcWord.Length + 1) = lcWord + "." Then
                loStringBuilder = oApp.StuffFromStringBuilder(loStringBuilder, 1, 1, "[")
                loStringBuilder = oApp.StuffFromStringBuilder(loStringBuilder, lcWord.Length + 2, 1, "]")
            End If

            ' Field in update
            loStringBuilder.Replace("," + lcWord + "=", ",[" + lcWord + "]=")

        Next

        ' For each reserved words for fields only
        For lnCounter = 1 To 1
            lcWord = laReservedWordForFieldOnly(lnCounter)

            ' Field
            loStringBuilder.Replace("," + lcWord + ".", ",[" + lcWord + "].")
            loStringBuilder.Replace("." + lcWord + ",", ".[" + lcWord + "],")
            loStringBuilder.Replace("." + lcWord + " ", ".[" + lcWord + "] ")
            loStringBuilder.Replace(" " + lcWord + ",", " [" + lcWord + "],")
            loStringBuilder.Replace("," + lcWord + ")", ",[" + lcWord + "])")

            ' INNER JOIN or AS clause in the fields
            loStringBuilder.Replace(" " + lcWord + ".", " [" + lcWord + "].")
            loStringBuilder.Replace("." + lcWord + "=", ".[" + lcWord + "]=")
            loStringBuilder.Replace("=" + lcWord + ".", "=[" + lcWord + "].")
            loStringBuilder.Replace("." + lcWord + " ", ".[" + lcWord + "] ")

            ' Where
            loStringBuilder.Replace("(" + lcWord + ".", "([" + lcWord + "].")
            loStringBuilder.Replace("." + lcWord + ")", ".[" + lcWord + "])")
            loStringBuilder.Replace("." + lcWord + ">", ".[" + lcWord + "]>")
            loStringBuilder.Replace("." + lcWord + "<", ".[" + lcWord + "]<")
            loStringBuilder.Replace("." + lcWord + "=", ".[" + lcWord + "]=")

            ' Update
            loStringBuilder.Replace(" " + lcWord + "=", " [" + lcWord + "]=")

            ' Order
            loStringBuilder.Replace("," + lcWord + " ", ",[" + lcWord + "] ")
            loStringBuilder.Replace("," + lcWord + ",", ",[" + lcWord + "],")

            ' Check for the last occurrence
            If Mid(loStringBuilder.ToString, loStringBuilder.Length - lcWord.Length) = "." + lcWord Then
                loStringBuilder.Remove(loStringBuilder.Length - 1 - lcWord.Length, lcWord.Length)
                loStringBuilder.Append("[" + lcWord + "] ")
            End If

            ' Check for the first occurrence
            If Mid(loStringBuilder.ToString, 1, lcWord.Length + 1) = lcWord + "." Then
                loStringBuilder = oApp.StuffFromStringBuilder(loStringBuilder, 1, 1, "[")
                loStringBuilder = oApp.StuffFromStringBuilder(loStringBuilder, lcWord.Length + 2, 1, "]")
            End If

            ' Field in update
            loStringBuilder.Replace("," + lcWord + "=", ",[" + lcWord + "]=")

        Next

        Return loStringBuilder.ToString
    End Function
The part I was wondering about to see if it can be optimized was:
            ' Check for the last occurrence
            If Mid(loStringBuilder.ToString, loStringBuilder.Length - lcWord.Length) = "." + lcWord Then
                loStringBuilder.Remove(loStringBuilder.Length - 1 - lcWord.Length, lcWord.Length)
                loStringBuilder.Append("[" + lcWord + "] ")
            End If

            ' Check for the first occurrence
            If Mid(loStringBuilder.ToString, 1, lcWord.Length + 1) = lcWord + "." Then
                loStringBuilder = oApp.StuffFromStringBuilder(loStringBuilder, 1, 1, "[")
                loStringBuilder = oApp.StuffFromStringBuilder(loStringBuilder, lcWord.Length + 2, 1, "]")
            End If
Michel Fournier
Level Extreme Inc.
Designer, architect, owner of the Level Extreme Platform
Subscribe to the site at https://www.levelextreme.com/Home/DataEntry?Activator=55&NoStore=303
Subscription benefits https://www.levelextreme.com/Home/ViewPage?Activator=7&ID=52
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform