How to mass-replace variable string in database?

Question

I need to mass-replace a string in my forum database via phpMyAdmin, but I don't know how.

I want to replace:

[member=1]username[/member]

with

[url=https://www.website.xxx/index.php?action=profile;u=1]username[/url]

My problem is with that "1", which changes with each username.

Here is what I have tried:

UPDATE messages
SET body = replace(body,"[member=","[url=https://www.website.xxx/index.php?action=profile;u=")

Answer 1

I don't have a MySQL installed here but I read up it works there as well, as Phil said, you need regex

To read about regex replace, go here To check how it works, go here and paste what you need to find and replace.

Although it is not clear what part exactly you want to replace

The whole thing with number after '=' [member=ANY_NUMBER]username[/member] or replace the ANY_NUMBER with [...]username[/url]?

If you need to replace the whole thing use this regex \[member=\d\]username\[\/member\] the \d is a number, but only one, if it is multiple digits, do \d+

so it would be

UPDATE messages 
SET body = 
  REGEXP_REPLACE(body, 
    '\[member=\d+\]username\[\/member\]', 
    'REPLACE_WITH_TEXT_HERE'
  )

Before actually updating, do run a select to verify it works as intended

if you need to replace the ANY_NUMBER part for anything else so it would be

Warning: db engines can reference capture groups differently, so check with select that it works before doing an update

UPDATE messages 
SET body = 
  REGEXP_REPLACE(body, 
    '(\[member=)\d+(\]username\[\/member\])', 
    '\1REPLACE_WITH_TEXT_HERE\2'
  )

The \1 and \2 are important and are called capture groups, we can divide a search in parts and use that as we want, for ex do \2\1 to reverse order.

Before actually updating, do run a select to verify it works as intended

UPDATE:new information

eliminate everything but the username, i.e., change this: [member=ANY_NUMBER]username[/member] to this: username.

That is a subset of described up there

So we need to find [member=ANY_NUMBER]username[/member] and get only the username I strongly advise you to look up what regex is, how to use it to replace and how to use captive groups.

This is regex to 'select' user from body \[member=\d+\](.+)\[\/member\]

How it works? It finds anything that looks like [member=ANY_NUMBER] then it looks to find [/member], and ANYTHING in between is captured by a capture group (.+) it literally says here: any sting of any chars of any length.

This is how it works: link to what you see https://regex101.com/r/HSd2WA/1

update SQL:

UPDATE messages 
SET body = 
  REGEXP_REPLACE(body, 
    '\[member=\d+\](.+)\[\/member\]', 
    '\1'
  )

VERIFYING THAT IT WORKS

How? Do a select, that's how!

I created a table with ID and body, populated with what you can find in the regex link.

 id |                             body
----+---------------------------------------------------------------
  1 | sdfdfsfdsasfda[member=1]merof[/member]safdfdsafdsfdsa
  2 | sfdafdsafdsa[member=10]Fraga Campos[/member]sfdasfda
  3 | asdffdsafdsa[member=300]Campos[/member]afsfdsafds
  4 | dfsvnosringtpoeringer[member=10000]Fraga[/member]sdafdsdfgdfg
  5 | dsfggfdsgfds[member=10000]dude[/member]
  6 | sdgf[member=10000]user[/member]sfd
  7 | dfg[member=10000]00)_09824;sd-2=3#_@$JFuser[/member]df
(7 rows)

Now I do a select the '!!!\1!!!' is there replace value, where \1 is capture group, I added !!! for it to be easier to see.

select REGEXP_REPLACE(body, 
    '\[member=\d+\](.+)\[\/member\]', 
    '!!!\1!!!'
  )
from messages  

Gives me

                regexp_replace
----------------------------------------------
 sdfdfsfdsasfda!!!merof!!!safdfdsafdsfdsa
 sfdafdsafdsa!!!Fraga Campos!!!sfdasfda
 asdffdsafdsa!!!Campos!!!afsfdsafds
 dfsvnosringtpoeringer!!!Fraga!!!sdafdsdfgdfg
 dsfggfdsgfds!!!dude!!!
 sdgf!!!user!!!sfd
 dfg!!!00)_09824;sd-2=3#_@$JFuser!!!df
(7 rows)