My Replace function with Regular Expression support

[Robert Neville]

I had an idea for a replace function that would iterate through a
table matching and replacing data. The novelty of the function
relates to using regular expressions and placing all find values in a
separate table. The function would read through one table and change
values in another table. Below you will find my rough draft. I do not
know if this approach is efficiency, yet it seems to work. I have not
intensively tested it. I would like to get the thoughts of some
developers before implementing this approach.

Public Function fnReplace()

Dim X As Integer
Dim dbs As Database
Dim rstPatterns As Recordset
Dim rst As Recordset
Dim strFind As String
Dim strReplace As String
Dim blnMatchCase As Boolean

Set dbs = CurrentDb
Set rstPatterns = dbs.OpenRecordset("tblReplace")

Dim objReg As VBScript_RegExp_55.RegExp
Set objReg = New VBScript_RegExp_55.RegExp

With rstPatterns
rstPatterns.MoveFirst
'loop thru each row
Do While Not rstPatterns.EOF
Set rst = dbs.OpenRecordset(rstPatterns!tables)
With rst
'loop thru each col
For X = 0 To rst.Fields.Count - 1
If rst.Fields(X).Name = rstPatterns!FieldName Then
Do While Not rst.EOF
' Set pattern.
objReg.PATTERN = rstPatterns!PATTERN
blnMatchCase = rstPatterns!Case
' Set case insensitivity
objReg.IgnoreCase = Not (blnMatchCase)
objReg.Global = True
strReplace = rstPatterns!Replace
rst.Edit
rst.Fields(X) = objReg.Replace(rst.Fields(X),
strReplace)
rst.Update
.MoveNext
Loop
Set objReg = Nothing
strReplace = ""
End If
Next
End With
.MoveNext
Loop
End With

dbs.Close
Set objReg = Nothing
Set rstPatterns = Nothing
Set rst = Nothing
Set dbs = Nothing

End Function

 

[John Nurick]

Hi Robert,

If I read you right, you're effectively putting a series of "replace"
instructions in one table (table, field, pattern to find, replacement
string) and then using your function to apply them. Good thinking.

One comment: this line in your For .. Next loop

Set objReg = Nothing

seems wrong. You don't want to be destroying the regex object there: you
created it before entering the outer Do While Not ... Loop loop, and you
need each time through the loop.

For efficiency, I'd try to minimise the number of times I set the
Pattern property of the regex object. Setting these forces a "recompile"
of the regex object (to handle the new situation). Here, it looks as if
you can move these operations out of the innermost loop:

If rst.Fields(X).Name = rstPatterns!FieldName Then
' Set pattern.
objReg.PATTERN = rstPatterns!PATTERN
blnMatchCase = rstPatterns!Case
' Set case insensitivity
objReg.IgnoreCase = Not (blnMatchCase)
objReg.Global = True
Do While Not rst.EOF
...

I had an idea for a replace function that would iterate through a
table matching and replacing data. The novelty of the function
relates to using regular expressions and placing all find values in a
separate table. The function would read through one table and change
values in another table. Below you will find my rough draft. I do not
know if this approach is efficiency, yet it seems to work. I have not
intensively tested it. I would like to get the thoughts of some
developers before implementing this approach.

Public Function fnReplace()

Dim X As Integer
Dim dbs As Database
Dim rstPatterns As Recordset
Dim rst As Recordset
Dim strFind As String
Dim strReplace As String
Dim blnMatchCase As Boolean

Set dbs = CurrentDb
Set rstPatterns = dbs.OpenRecordset("tblReplace")

Dim objReg As VBScript_RegExp_55.RegExp
Set objReg = New VBScript_RegExp_55.RegExp

With rstPatterns
rstPatterns.MoveFirst
'loop thru each row
Do While Not rstPatterns.EOF
Set rst = dbs.OpenRecordset(rstPatterns!tables)
With rst
'loop thru each col
For X = 0 To rst.Fields.Count - 1
If rst.Fields(X).Name = rstPatterns!FieldName Then
Do While Not rst.EOF
' Set pattern.
objReg.PATTERN = rstPatterns!PATTERN
blnMatchCase = rstPatterns!Case
' Set case insensitivity
objReg.IgnoreCase = Not (blnMatchCase)
objReg.Global = True
strReplace = rstPatterns!Replace
rst.Edit
rst.Fields(X) = objReg.Replace(rst.Fields(X),
strReplace)
rst.Update
.MoveNext
Loop
Set objReg = Nothing
strReplace = ""
End If
Next
End With
.MoveNext
Loop
End With

dbs.Close
Set objReg = Nothing
Set rstPatterns = Nothing
Set rst = Nothing
Set dbs = Nothing

End Function

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

 

[Robert Neville]

If I read you right, you're effectively putting a series of "replace"
instructions in one table (table, field, pattern to find, replacement
string) and then using your function to apply them.
Yep

Good thinking. Thanks


One comment: this line in your For .. Next loop
seems wrong. You don't want to be destroying the regex object there: you
created it before entering the outer Do While Not ... Loop loop, and you
need each time through the loop.

For efficiency, I'd try to minimise the number of times I set the
Pattern property of the regex object. Setting these forces a "recompile"
of the regex object (to handle the new situation). Here, it looks as if
you can move these operations out of the innermost loop:

If rst.Fields(X).Name = rstPatterns!FieldName Then
' Set pattern.
objReg.PATTERN = rstPatterns!PATTERN
blnMatchCase = rstPatterns!Case
' Set case insensitivity
objReg.IgnoreCase = Not (blnMatchCase)
objReg.Global = True
Do While Not rst.EOF

These suggestions are great. The second Do While Loop was an
after-thought and I hesitate placing another loop; so I put together
that of block of code in haste. You were absolutely correct about
redeclaring the objReg in the inner Do While Loop.

FYI
This function helps me correct inconsistencies in my convert-case
function. Plus, it addresses known common misspelling. I got the idea
from Office's autocorrect feature.

Quick question
I am having trouble passing spaces from the table, tblReplace, to the
function. The table fields automatically trim trailing space from
data. Let me explain with an example

the Replacement Table looks like the list below
Table FieldName Pattern Replace Case
tblTEMP CompName \s+Of\s+ of True

How would you suggestion dealing with spaces in the replacement
argument?

 

[John Nurick]

ACCESS trims trailing spaces.
JET tables don't trim trailing spaces.
(some) SQL tables trim trailing spaces.

So if you are using Access and JET, one solution is
to use DAO/ADO code to write the (trailing spaces) to
the table. If you are not going to do it often,
you could use " a string " in some code. If you are
going to create strings often, you could have a form,
using "_" instead of " ", and write code that would
replace "_" with " ", and then write the string to the
table, rather than using a form directly bound to the
table.

An alternative would be to substitute a token for a trailing space,
using something never appear in your data for some other reason. E.g.

Want Store
C-style "aaa " "aaa\x20"
HTML "aaa " "aaa&0032;"

and then when retrieving the value from the recordset use Replace() to
restore the trailing space.

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

 

[Robert Neville]

An alternative would be to substitute a token for a trailing space,
using something never appear in your data for some other reason. E.g.

Want Store
C-style "aaa " "aaa\x20"
HTML "aaa " "aaa&0032;"

and then when retrieving the value from the recordset use Replace() to
restore the trailing space.

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

John,

Well, the workaround was exactly as you described. I changed the
Replace variable to this line below.

strReplace = Replace(rstPatterns!Replace, "|", "")

The token is not exactly intuitive, yet it was the only approach that
worked. I tried several approaches as the list below with little
success.

Pattern Replace
\+Of\s+ " of " Did not work
\+Of\s+ \040of\040 Did not work
\+Of\s+ ' of ' Did not work
\+Of\s+ Chr(32)ofChr(32) Did not work
\+Of\s+ | of | Worked

I even tried setting the field's format property to literal spaces
without any luck.

David,

Thanks for joining the discussion.