Mysql query string replace

Question

Possible Duplicate:
How to do a regular expression replace in MySQL?

I try to replace strings that start with '?campaign=qwertysomerandomtext"' and replace it with '"'.

I tried using wildcards for this like '?campaign=%_" and replace with '"'. But found out Wildcards dont work with replace.

Is there any other way to deal with that problem? I thought about using something like substring from to. But found nothing that works for me so far.

Hope someone can help me with that Problem.

regards bnz

Is there a way you could just preprocess the table? (update x set col = " where col like '?campaign=%') — stb, Jul 25 '12 at 12:55
@lqez, no duplicate, as simple replacement doesn't need regular expressions. — MvG, Jul 25 '12 at 13:56

score 1 · Accepted Answer · answered Jul 25 '12 at 13:55

I try to replace strings that start with '?campaign=qwertysomerandomtext"' and replace it with '"'.

Taking this request literally, it becomes

UPDATE t SET s = '"' WHERE s = '?campaign=qwertysomerandomtext"'

I've copied the double quotation mark from your question, both at the end of the search string and inside the replacement string. If this is an error, please adjust.

If the random text is a placeholder and should be a wildcard, as stated in your second paragraph, then use

UPDATE t SET s = '"' WHERE s = '?campaign=%_"'

If the text you want to replace is at the end of some other string, instead of at the beginnning the way you asked, then you can do

UPDATE t SET s = LEFT(s, LOCATE('?campaign=', s)) WHERE s = '?campaign=%_"'

This identifies the position of the campaign stuff, and removes that and everything following that.

No regular expressions needed. In general, you can achieve pretty much by combining LOCATE, LEFT, RIGHT, SUBSTR and CONCAT.

Mysql query string replace

1 Answers1