Find and replace of portions of text strings

[Karen]

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.

example
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.B ody2 =
RegExpReplace(R egExpReplace(Re gExpReplace(Reg ExpReplace([body],"http://[^
]*","http://",True,Fals e),"[\d\.\-\(\)]{7,}","######## ##",True,False) ,"\d{5}","####" ,True,False),"[""\d]{4,6}","####",T rue,False)
WHERE (((combined_log s.Body2) Is Null) AND
((combined_logs .InOut)="Out")) ;

VBA Code:

Function RegExpReplace(L ookIn As String, PatternStr As String, Optional
ReplaceWith As String = "", _
Optional ReplaceAll As Boolean = True, Optional MatchCase As Boolean =
True)
On Error GoTo RegExpReplace_E rror

Dim RegX As Object

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

RegExpReplace = RegX.Replace(Lo okIn, ReplaceWith)

Set RegX = Nothing
RegExpReplace_E xit:
Exit Function

RegExpReplace_E rror:
MsgBox "Unexpected error - " & Err.Number & vbCrLf & vbCrLf & Error$,
vbExclamation, "Access9db - RegExpRep"
Resume RegExpReplace_E xit

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.

Thanks,

Karen

Navigate: Previous Message · Next Message

[Bill Karwin]

Karen wrote:

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

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

mysql> SELECT REPLACE('www.my sql.com', 'w', 'Ww');
-> 'WwWwWw.mysql.c om'

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
statements:

SELECT CONCAT(
'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.

Regards,
Bill K.