Find and Replace (1 Viewer)

[alexfwalker81]

I have two tables in my database, one with a long text field in which I'd like to perform a find and replace.

However, the find and replace is not static. So in my second table, I have the values to find and the values to replace, e.g.;

Find	Replace
A	S
B	T
C	U
D	V
E	W

How can I write a function which would look for instances of 'Find' and then replace with the values in 'Replace'?

 

[theDBguy]

I guess one way is to use VBA to loop through your Find/Replace table and execute an UPDATE query for each one with the Replace() function.

 

[cheekybuddha]

Presumably this won't be an operation that you will have to perform often, so without worrying about performance you can create a function that opens a recordset based on SecondTable and loops the recordset and performs the Replace() as you go.

It could look something like:

Function FindAndReplace(input As Variant) As String

  Const FIND_REPLACE_VALUES As String = "SELECT [Find], [Replace] FROM SecondTable;"
 
  If Len(input & vbNullString) = 0 Then Exit Function    ' Bail if Null or empty string is passed
  With CurrentDb.OpenRecordset(FIND_REPLACE_VALUES)
    Do While Not .EOF
      input = Replace(input, .Fields("Find"), .Fields("Replace"))
      .MoveNext
    Loop
    .Close
  End With
  FindAndReplace = input

End Function

Then, having backed up your first table first, you can do the update like:

UPDATE FirstTable
SET
  YourField = FindAndReplace(YourField)
;

 

[cheekybuddha]

@theDBguy 's method would probably be more efficient

 

[GPGeorge]

I have two tables in my database, one with a long text field in which I'd like to perform a find and replace.

However, the find and replace is not static. So in my second table, I have the values to find and the values to replace, e.g.;

Find	Replace
A	S
B	T
C	U
D	V
E	W

How can I write a function which would look for instances of 'Find' and then replace with the values in 'Replace'?

Out of curiosity, what kind of process requires regular modification of already stored values? Your "sample" table is a simple letter-for-letter substitution, so it effectively conceals the actual context.

 

[alexfwalker81]

Presumably this won't be an operation that you will have to perform often, so without worrying about performance you can create a function that opens a recordset based on SecondTable and loops the recordset and performs the Replace() as you go.

Correct, it's not a frequent task. I'm in the process of a website migration where category URLs have changed from A to S, but not in a totally predictable way. I have a table which shows the old target URL and the new target URL, plus a table of old blog posts where the old target URLs exist.

Before anyone says 301 redirect - yes, you're totally right - but good SEO practice would have us clean up all the old URLs, rather than rely on 301's, so that's my task.

I'll give your solution a whirl. I did find an old version of some code that I wrote in a DB a while back, but as I've deployed I'm getting runtime errors. Sadly I'm not skilled enough to debug things very effectively!

 

[alexfwalker81]

Function FindAndReplace(input As Variant) As String

  Const FIND_REPLACE_VALUES As String = "SELECT [Find], [Replace] FROM SecondTable;"
 
  If Len(input & vbNullString) = 0 Then Exit Function    ' Bail if Null or empty string is passed
  With CurrentDb.OpenRecordset(FIND_REPLACE_VALUES)
    Do While Not .EOF
      input = Replace(input, .Fields("Find"), .Fields("Replace"))
      .MoveNext
    Loop
    .Close
  End With
  FindAndReplace = input

End Function

Then, having backed up your first table first, you can do the update like:

UPDATE FirstTable
SET
  YourField = FindAndReplace(YourField)
;

I'm having some issues compiling this - it doesn't like 'input'

 

[GPGeorge]

I'm having some issues compiling this - it doesn't like 'input'

Input is probably a reserved word. Use something else, like "strURL", which will not be ambiguous.

So if the only change is from "A" to "S", this is not going to be as easy as if the strings to change were unique, multi-character strings. You can not just substitute one letter for the other without changings "A"s in other places that you don't want to change. We'd have to see some full URLs to offer specifics, of course.

Is there a delimiter or other string of characters which you can use rather than just "A" and "S"?

 

[alexfwalker81]

Input is probably a reserved word. Use something else, like "strURL", which will not be ambiguous.

So if the only change is from "A" to "S", this is not going to be as easy as if the strings to change were unique, multi-character strings. You can not just substitute one letter for the other without changings "A"s in other places that you don't want to change. We'd have to see some full URLs to offer specifics, of course.

Is there a delimiter or other string of characters which you can use rather than just "A" and "S"?

Thanks George, I realise the difficulty around substrings - I'm confident I've got this covered. I've changed 'input' to 'strinput' but just get 'Invalid use of Null' when I run it.

 

[GPGeorge]

Thanks George, I realise the difficulty around substrings - I'm confident I've got this covered. I've changed 'input' to 'strinput' but just get 'Invalid use of Null' when I run it.

You'll need to handle the cases where the category is not present in the URL.

 

[cheekybuddha]

I've changed 'input' to 'strinput' but just get 'Invalid use of Null' when I run it.

Can you post the VBA function you've got?

 

[GPGeorge]

Thanks George, I realise the difficulty around substrings - I'm confident I've got this covered. I've changed 'input' to 'strinput' but just get 'Invalid use of Null' when I run it.

I want to comment on the practice of "simplifying sample data" for the sake of questions in online forums.

Over the decades, I've observed that a lot of times, people present so-called simplified descriptions and sample data and sample table structures, thinking that will make it easier to convey the essence of a problem. That practice is far more likely to lead to miscommunication and misunderstanding, though. Here, for example, if you truly are looking only for "A" and "S", not some more accurate terms, the problem of correct matches is really significant. If, on the other hand, you "simplified" the sample for this question, doing so introduced an unnecessary ambiguity that would otherwise not need to be addressed.

I apologize if I'm reading more into the use of single letters than is relevant. It's just that 2 decades of participating in Access forums like this one has made me uncomfortable in accepting things at face value.

 

[alexfwalker81]

Can you post the VBA function you've got?

Function FindAndReplace(strinput As Variant) As String

  Const FIND_REPLACE_VALUES As String = "SELECT [Target], [New target] FROM Subs;"
 
  If Len(strinput & vbNullString) = 0 Then Exit Function    ' Bail if Null or empty string is passed
  With CurrentDb.OpenRecordset(FIND_REPLACE_VALUES)
    Do While Not .EOF
      strinput = Replace(strinput, .Fields("Target"), .Fields("new target"))
      .MoveNext
    Loop
    .Close
  End With
  FindAndReplace = strinput

End Function

When I run that as a select query, I see;

Debugging gives the following;

 

[cheekybuddha]

Can you re-run it using:

Function FindAndReplace(strinput As Variant) As String

        Const FIND_REPLACE_VALUES As String = "SELECT [Target], [New target] FROM Subs;"
      
10    On Error GoTo Err_FindAndReplace

20      If Len(strinput & vbNullString) = 0 Then Exit Function    ' Bail if Null or empty string is passed
30      With CurrentDb.OpenRecordset(FIND_REPLACE_VALUES)
40        Do While Not .EOF
50          Debug.Print "strinput: " & Nz(strinput, "Null"), "target: " & Nz(.Fields("Target"), "Null"), "New Target: " & Nz(.Fields("New Target"), "Null")
60          strinput = Replace(strinput, .Fields("Target"), .Fields("new target"))
70          .MoveNext
80        Loop
90        .Close
100     End With
110     FindAndReplace = strinput

Exit_FindAndReplace:
120     Exit Function

Err_FindAndReplace:
130     Select Case Err.Number
        Case Else
140       MsgBox "Error No.: " & Err.Number & vbNewLine & vbNewLine & _
                 "Description: " & Err.Description & vbNewLine & vbNewLine & _
                 "Function: FindAndReplace" & vbNewLine & _
                 IIf(Erl, "Line No: " & Erl & vbNewLine, "") & _
                 "Module: " & Application.VBE.ActiveCodePane.CodeModule.Name, , _
                 "Error: " & Err.Number
150     End Select
160     Resume Exit_FindAndReplace

End Function

 

[alexfwalker81]

Of course, done.

 

[GPGeorge]

Function FindAndReplace(strinput As Variant) As String

  Const FIND_REPLACE_VALUES As String = "SELECT [Target], [New target] FROM Subs;"
 
  If Len(strinput & vbNullString) = 0 Then Exit Function    ' Bail if Null or empty string is passed
  With CurrentDb.OpenRecordset(FIND_REPLACE_VALUES)
    Do While Not .EOF
      strinput = Replace(strinput, .Fields("Target"), .Fields("new target"))
      .MoveNext
    Loop
    .Close
  End With
  FindAndReplace = strinput

End Function

When I run that as a select query, I see;

View attachment 116834


Debugging gives the following;

View attachment 116835

The string in question must include the "Target" value in order for the Replace function to work without that error.
David's method will handle that. Here's another approach.

Check for its presence before attempting the replacement.
<snippet>
Do While Not .EOF
If Instr(strinput, .Fields("Target")>0 Then
strinput = Replace(strinput, .Fields("Target"), .Fields("new target"))
End If
.MoveNext
Loop
</snippet>

 

[cheekybuddha]

Of course, done.

What was the last entry in the Immediate Window (Ctrl+G) after you get the error?

 

[cheekybuddha]

The string in question must include the "Target" value in order for the Replace function to work without that error.

Must it?

I thought that if target doesn't exist within then no replace takes place and you are left with original input

 

[GPGeorge]

Must it?

I thought that if target doesn't exist within then no replace takes place and you are left with original input

Hmm. You are right, of course. The error is occurring on that line in the posted code; I leapt to the wrong conclusion. The Null error must be coming from something else.

So the only other possibility I can see is that strinput itself is null.

 

[cheekybuddha]

I'm guessing that @alexfwalker81 is trying to remove completely some string in the field (ie replace with nothing), but the field in the table (new target) is Null rather than a zls.

Again guessing, but the answer is probably:

' ...
      strinput = Replace(strinput, .Fields("Target"), Nz(.Fields("new target"), vbNullString))
' ...

If Target is Null then you're really in trouble!