Find and replace of portions of text strings

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
I am very new to MySQL- I currently have an VBA module using a VBSCript
that will find different aspects of a text string that are a unique
text string and turn it into a not so unique text string. Which allows
for a more accurate count of text strings.

Item# Text string CountTExt
1 Now is the time to call me at 800-001-0055. 1
2 Now is the time to call me at 800-001-7777. 1

Convert to:
Item# Text string
1 & 2 Now is the time to call me at #########. 2.

Current SQL statement run in Access 2K3 - against a linked MYSQL table.
- Runs very slowly. Would like to move this to MYSQL if possible.

UPDATE combined_logs SET combined_logs.Body2 =
WHERE (((combined_logs.Body2) Is Null) AND

VBA Code:

Function RegExpReplace(LookIn As String, PatternStr As String, Optional
ReplaceWith As String = "", _
Optional ReplaceAll As Boolean = True, Optional MatchCase As Boolean =
On Error GoTo RegExpReplace_Error

Dim RegX As Object

Set RegX = CreateObject("VBScript.RegExp")
With RegX
.Pattern = PatternStr
.Global = ReplaceAll
.IgnoreCase = Not MatchCase
End With

RegExpReplace = RegX.Replace(LookIn, ReplaceWith)

Set RegX = Nothing
Exit Function

MsgBox "Unexpected error - " & Err.Number & vbCrLf & vbCrLf & Error$,
vbExclamation, "Access9db - RegExpRep"
Resume RegExpReplace_Exit

End Function

I understand that the replace function within MYSql replaces the intire
string and the regexp returns the value of True or False.

Is there a way to duplicate the above function within MYSql.



Navigate: Previous Message Next Message

Re: Find and replace of portions of text strings

Karen wrote:
Quoted text here. Click to load it

No -- in MySQL, the REPLACE() function takes three arguments:  the
string to search, the substring to match, and the replacement string.

   mysql> SELECT REPLACE('', 'w', 'Ww');
         -> ''

But that doesn't help you, because it doesn't replace regular expression
patterns, it only replaces fixed strings.

I think you are stuck doing your pattern substitution in application
code.  Using MS Access & VB might not be the fastest solution.  You
might consider Perl or Java.

You might also be able to speed up the operation by executing the UPDATE
against only the rows where the patterns occur:

   UPDATE ...
   WHERE body REGEXP 'pattern'

At least that won't waste time doing all those regexp replacements on
rows where the pattern doesn't occur.

Another option I can think of is to generate a SQL script of many UPDATE

     'UPDATE combined_logs SET body2 = \',
     REPLACE(body, '\'', '\'\''),
     '\' WHERE primaryKey = ', primaryKey, ';'
   ) AS update_script
   FROM combined_logs
   WHERE body REGEXP 'pattern'

Then run the resulting script using the "mysql" command-line tool.  This
should run much faster than doing it in VB code.

Bill K.

Site Timeline