469,916 Members | 2,251 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,916 developers. It's quick & easy.

Find and replace of portions of text strings

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 =
RegExpReplace(RegExpReplace(RegExpReplace(RegExpRe place([body],"http://[^
]*","http://",True,False),"[\d\.\-\(\)]{7,}","##########",True,False),"\d{5}","####",True ,False),"[""\d]{4,6}","####",True,False)
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

Apr 4 '06 #1
1 2614
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.mysql.com', 'w', 'Ww');
-> 'WwWwWw.mysql.com'

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:

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.
Apr 4 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Jeffrey | last post: by
5 posts views Thread by peter | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.