On the first glimpse, there is no direct way to do a replace substring in LotusScript like in formula using @ReplaceSubstring. Even the
IBM Technote #1102507 suggests using "instr", "left" and "mid".
There is a much faster and easy way to use accomplish this task by using the function replace:
Replace(sourceArray, findArray, replacementArray [, start [, count [, compMethod]]])
This function also works for strings.
The code is even faster too:
- 3773 ms - FindReplace_IBM
- 2719 ms - FindReplace_Tuned
- 539 ms - Replace
- 1469 ms - Replace on Array
- 7473 ms - FindReplace_IBM - 2 Values
- 6688 ms - FindReplace_Tuned - 2 Values
- 1102 ms - Replace - 2 Values
- 1871 ms - Replace on Array - 2 Values
Demo Code: Usage Replace
Sub Initialize
Dim result As Variant
' Replace single Value
result = Replace ("alice bar alicebar", "alice", "foo")
Print "1: " + result ' result = "foo bar foobar"
' Replace two Values
result = Replace ("alice bob alicebob", _
Split("alice bob"), Split("foo bar"))
Print "2: " + result ' result = "foo bar foobar"
' Replace two Values, with one
result = Replace ("alice bar bobbar", Split("alice bob"), "foo")
Print "3: " + result ' result = "foo bar foobar"
' If replacmentArray is larger than findArray,
' the Values will be ignored
result = Replace ("alice bar bobbar", _
Split("alice bob"), Split("foo foo bar"))
Print "4: " + result ' result = "foo bar foobar"
' For Replacing Blank use Delimiter in Split
result = Replace ("alice bar alice bar", _
Split("alice: ",":"), Split("foo:",":"))
Print "5: " + result ' result = "foo bar foobar"
' Multi Level Replacments
' "alicebob bar bobbobbar" -> "bobbob bar bobbobbar"
' "bobbob bar bobbobbar" -> "foo bar foobar"
result = Replace ("alicebob bar bobbobbar", _
Split("alice bobbob"), Split("bob foo"))
Print "6: " + result ' result = "foo bar foobar"
Dim resultArray As Variant
' Replace Array Substring
resultArray = Replace(Split("alice bob alicebob"), _
Split("alice bob"), Split("foo bar"))
Print "6: " + "[""" + Join(resultArray,""", """) + """]"
' resultArray = "["foo", "bar", "foobar"]"
End Sub
Demo Code: Perfomance
Option Public
Option Declare
Sub Initialize
Const LOOP_MAX = 100000
Dim i As Long
Dim timerStart As Single
Dim timerResult As Single
Dim result As Variant
Dim resultArray As Variant
' Classic IBM Method
TimerStart = Timer
For i = 1 To LOOP_MAX
result = FindReplace_IBM("faa bar faabar faa bar faabar faa bar faabar", "aa", "oo")
Next
timerResult = (Timer - timerStart) * 1000 ' milliseconds
print Format$(timerResult, "###0") + " ms - FindReplace_IBM " + " - Result: " + result
' IBM Method tuned
TimerStart = Timer
For i = 1 To LOOP_MAX
result = FindReplace_Tuned("faa bar faabar faa bar faabar faa bar faabar", "aa", "oo")
Next
timerResult = (Timer - timerStart) * 1000 ' milliseconds
print Format$(timerResult, "###0") + " ms - FindReplace_Tuned " + " - Result: " + result
' Replace
TimerStart = Timer
For i = 1 To LOOP_MAX
result = Replace("faa bar faabar faa bar faabar faa bar faabar", "aa", "oo")
Next
timerResult = (Timer - timerStart) * 1000 ' milliseconds
print Format$(timerResult, "###0") + " ms - Replace " + " - Result: " + result
' Replace on Array
TimerStart = Timer
For i = 1 To LOOP_MAX
resultArray = Replace(Split("faa bar faabar faa bar faabar faa bar faabar"), "aa", "oo")
Next
timerResult = (Timer - timerStart) * 1000 ' milliseconds
print Format$(timerResult, "###0") + " ms - Replace on Array " + " - Result: [""" + Join(resultArray,""", """) + """]"
' Classic IBM Method - 2 Values
TimerStart = Timer
For i = 1 To LOOP_MAX
result = FindReplace_IBM("faa bar faabar faa bar faabar faa bar faabar", "aa", "oo")
result = FindReplace_IBM(result, "oo", "uu")
Next
timerResult = (Timer - timerStart) * 1000 ' milliseconds
print Format$(timerResult, "###0") + " ms - FindReplace_IBM - 2 Values " + " - Result: " + result
' IBM Method tuned - 2 Values
TimerStart = Timer
For i = 1 To LOOP_MAX
result = FindReplace_Tuned("faa bar faabar faa bar faabar faa bar faabar", "aa", "oo")
result = FindReplace_IBM(result, "oo", "uu")
Next
timerResult = (Timer - timerStart) * 1000 ' milliseconds
print Format$(timerResult, "###0") + " ms - FindReplace_Tuned - 2 Values " + " - Result: " + result
' Replace - 2 Values
TimerStart = Timer
For i = 1 To LOOP_MAX
result = Replace("faa bar faabar faa bar faabar faa bar faabar", Split("aa oo"), Split("oo uu"))
Next
timerResult = (Timer - timerStart) * 1000 ' milliseconds
print Format$(timerResult, "###0") + " ms - Replace - 2 Values " + " - Result: " + result
' Replace on Array - 2 Values
TimerStart = Timer
For i = 1 To LOOP_MAX
resultArray = Replace(Split("faa bar faabar faa bar faabar faa bar faabar"), Split("aa oo"), Split("oo uu"))
Next
timerResult = (Timer - timerStart) * 1000 ' milliseconds
print Format$(timerResult, "###0") + " ms - Replace on Array - 2 Values " + " - Result: [""" + Join(resultArray,""", """) + """]"
End Sub
Function FindReplace_IBM (ByVal wholestring As Variant, find As String, ireplace As String) As String
' Source http://www-01.ibm.com/support/docview.wss?uid=swg21102507
Dim checkstring, saveleft, leftstring, rightstring As String
Dim n As Integer
checkstring=wholestring
saveleft=""
While InStr(1, checkstring, find) <> 0
n=InStr(1, checkstring, find)
leftstring = Left(checkstring, n-1)
rightstring=Right(checkstring, Len(checkstring)-n-Len(find)+1)
saveleft=saveleft+leftstring+ireplace
checkstring=rightstring
Wend
FindReplace_IBM = saveleft+checkstring
End Function
Function FindReplace_Tuned (paraString As String, paraFrom As String, paraTo As String) As String
' IBM Method tuned
Dim result As String
result = paraString
While InStr(result, paraFrom) <> 0
result = Left(result, InStr(result, paraFrom) - 1) & paraTo & Mid(result, InStr(result, paraFrom) + Len(paraFrom))
Wend
FindReplace_Tuned = result
End Function