mysql - SQL - From [spoiler:abcdefgh] to [spoiler] -

i've string in many records [spoiler:abcdefgh]. abcdefgh variable characters. want became [spoiler]. want remove :abcdefgh.

i know query is:

update post  set pagetext = replace(pagetext, ‘text want replace’, ‘replacament text’);

how can replace variable characters?

for example, if have [center:uezfbb79]texttt[/center:uezfbb79] want became: [center]texttt[/center]

"uezfbb79" not fixed, these random characters.

the following code should trick, assuming tags formatted. please test throroughly before running against production copy.

update post set pagetext = replace(pagetext, substr(pagetext, instr(pagetext,':'), instr(pagetext, ']') - instr(pagetext,':')), '') pagetext '[%:%]';

here's sql fiddle used very limited testing.

edit

this script should deal preceding [] situation:

update post set pagetext = replace(pagetext, substr(pagetext, @c1:=instr(pagetext,':'), locate(']', pagetext, @c1) - @c1), '') pagetext '[%:%]';

edit #2

my where clause restrictive. here's updated script:

update post set pagetext = replace(pagetext, substr(pagetext, @c1:=instr(pagetext,':'), locate(']', pagetext, @c1) - @c1), '') pagetext '%[%:%]%';

i changed fiddle update column (newtext), it's easier judge results.

Three