search and replace question

heidiland posted 3 months ago in General

Hi,

i am new to heidisql and have a search and replace question. I have a database with multible fields. I need to replace the following thing

img src="//mydomain/track.php?track=L2FjdGlvbi5tZXRhZmZpbGlhdGlvbi&id=93820fddd4b8bd8511f7ca9" style="width: 1px; height: 1px"

The encoded numbers always are different. The rest is always the same. Does anybody know how I can remove this from the database via heidisql? Does anybody know if a regex like (...) Capture everything enclosed works via the search and replace dialog box?

kind regards

Michael

ansgar posted 3 months ago

REGEXP_REPLACE is what you want: https://mariadb.com/kb/en/library/regexp_replace/

UPDATE foo
SET
  somecol = REGEXP_REPLACE(
    somecol,
    '<img src\=\"\/\/mydomain\/track.php\?track\=([a-zA-Z0-9]+)\" [^>]*>',
    '<div>New contents with old track id: \\1</div>'
  )
WHERE
  somecol LIKE '%whatever%'

Not sure about the backslash escaping, I think there always need to be two (not one!) backslashes in front of a reserved character like double quote.

Please login to leave a reply, or register at first.