Do you have a question? Post it now! No Registration Necessary. Now with pictures!
- Posted on
- Find and replace of portions of text strings
April 4, 2006, 3:34 pm
rate this thread
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
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
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")
.Pattern = PatternStr
.Global = ReplaceAll
.IgnoreCase = Not MatchCase
RegExpReplace = RegX.Replace(LookIn, ReplaceWith)
Set RegX = Nothing
MsgBox "Unexpected error - " & Err.Number & vbCrLf & vbCrLf & Error$,
vbExclamation, "Access9db - RegExpRep"
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
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');
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
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.
- » Americanas.com SALDO de TVs Com at 80% de Desconto (29798)
- — Newest thread in » MySQL Database Forum