1

I've this string in many records [spoiler:abcdefgh]. abcdefgh are variable characters. I want this to became [spoiler]. So I want to remove :abcdefgh.

I know that the query is:

UPDATE post 
SET pagetext = replace(pagetext, ‘text you want to replace’, ‘replacament text’);

How can I replace variable characters?

For example, if I have [center:uezfbb79]texttt[/center:uezfbb79] then I want this to became: [center]texttt[/center]

"uezfbb79" are not fixed, these are random characters.

testermaster
  • 1,031
  • 6
  • 21
  • 40
  • 1
    possible duplicate of [How to do a regular expression replace in MySQL?](http://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql) – flup May 18 '13 at 17:08
  • I'm not expert of mysql, so if possible i need someone that tell me "do this." It's an urgent think, it's the first time I'm using MYSQL, I don't know what to do.. :( – testermaster May 18 '13 at 17:13
  • @flup - Since MySql does not provide simple `REGEXP_REPLACE` functionality, it seems that each case might need to be treated differently. – PM 77-1 May 18 '13 at 17:47
  • maybe I can use any php script? – testermaster May 18 '13 at 17:55
  • If PHP supports replacement using *regular exceptions*, it may be a safer approach. Please see my SQL code posted below. – PM 77-1 May 18 '13 at 18:47
  • @PM77-1 In MYSQL, UDFs are the way to go if you need the full power of regular expressions inside the database, I think. – flup May 18 '13 at 23:42

1 Answers1

0

The following code should do the trick, assuming that all your tags are properly formatted. Please test throroughly before running against the production copy. 

UPDATE post
SET pagetext = REPLACE(pagetext, SUBSTR(pagetext, INSTR(pagetext,':'), INSTR(pagetext, ']') - INSTR(pagetext,':')), '')
WHERE pagetext LIKE '[%:%]';

Here's SQL Fiddle I used for very limited testing.

EDIT

This script should properly deal with preceding [] situation:

UPDATE post
SET pagetext = REPLACE(pagetext, SUBSTR(pagetext, @c1:=INSTR(pagetext,':'), LOCATE(']', pagetext, @c1) - @c1), '')
WHERE pagetext LIKE '[%:%]';

Updated Fiddle

EDIT #2

My WHERE clause was too restrictive. Here's a slightly updated script:

UPDATE post
SET pagetext = REPLACE(pagetext, SUBSTR(pagetext, @c1:=INSTR(pagetext,':'), LOCATE(']', pagetext, @c1) - @c1), '')
WHERE pagetext LIKE '%[%:%]%';

I changed the Fiddle to update another column (newtext), so it's easier to judge the results.

PM 77-1
  • 12,933
  • 21
  • 68
  • 111
  • In the records I've also some test like: "here's an example: if this works...". I think that your query will change also these records. Am I right? – testermaster May 18 '13 at 18:49
  • Yes, I've tried, and it changed "example:text" to "example]text" :( – testermaster May 18 '13 at 18:58
  • My query will look only at the fields that have opening and closing square brackets a with colon between them (`[... :...]`) (see `WHERE` clause) and then it will remove everything between `:` (inclusive) and `]` (exclusive). – PM 77-1 May 18 '13 at 19:00
  • You can go inside the `Fiddle` (link in my post) and play with the data. – PM 77-1 May 18 '13 at 19:01
  • My script, for example, should change `[:]example]` to `]]`. You may want to post some extreme samples of your real data to look at. – PM 77-1 May 18 '13 at 19:04
  • I've tried to do this again, but nothing to do. Maybe it crashes when it meets numbers? Oh, and thank you so much for the help! – testermaster May 18 '13 at 19:17
  • For example, this code has been converted: [center:uezfbb79]. This one hasn't been converted: [center:cdaz445u] . :/ – testermaster May 18 '13 at 19:23
  • THIS HASN'T BEEN EDITED: [size=7][color=#FF0000][center:2k7zks67]Procedura per trasferire i File Opzioni all'interno della PS3[/center:2k7zks67][/color][/size] [quote]text[/quote]. THIS HAS BEEN EDITED: [center:34jst32f][color=#FF0000][size=7]Procedura per trasferire un File mp3 all'interno della PS3 (Cori, Musiche)[/size][/color][/center:34jst32f] [quote]text[/quote] – testermaster May 18 '13 at 19:51
  • And what happens with URL? They're bulit in this way: [URL=http://url]url[/URL] :( – testermaster May 18 '13 at 20:01
  • You can enhance `WHERE` clause to exclude anything that has `URL` in it. – PM 77-1 May 18 '13 at 20:37
  • Ok, thank you again, but I still have this problem. I've tried also with SQL Fiddle and it's a problem. Do you know what could I do? THIS HASN'T BEEN EDITED: [size=7][color=#FF0000][center:2k7zks67]Procedura per trasferire i File Opzioni all'interno della PS3[/center:2k7zks67][/color][/size] [quote]text[/quote]. THIS HAS BEEN EDITED: [center:34jst32f][color=#FF0000][size=7]Procedura per trasferire un File mp3 all'interno della PS3 (Cori, Musiche)[/size][/color][/center:34jst32f] [quote]text[/quote] – testermaster May 18 '13 at 20:51
  • I understood that the problem is because of [] before [center: in the row, but I don't know how to solve the problem. :( – testermaster May 18 '13 at 21:16
  • I'm trying to focus only on the 'center' tag. Maybe it's easier in this way to solve the problem. Please let me know. THANK YOU FOR EVERYTHING! – testermaster May 18 '13 at 21:31
  • It doesn't work. It has changed also something that shouldn't be changed like [color=#555] tag :( – testermaster May 18 '13 at 21:51
  • I do not see the sample with `[color=#555]`. Is it well formed? Please post it. I believe the script worked for the samples you provided so far. Or have I missed something? – PM 77-1 May 18 '13 at 21:55
  • Well, there're hundred of topics. It was an example: after I imported the script color tags were wrong. I had start the your script many times, and EVERY times i started it, it edited something, so it means that something went wrong I think. And also, and most important, this HASN'T BEEN EDITED: [center:2a5rsgtu][img]http://img213.imageshack.us/img213/4826/masselsonxg8.png [/img][/center:2a5rsgtu]ol e Nelson possono ufficialmente rilasciare il loro File Opzioni per PS3! – testermaster May 18 '13 at 22:03
  • Also this is not edited with the scirpt: [center:2a5rsgtu][img]h. Thanks again for your time. – testermaster May 18 '13 at 22:09
  • Maybe we can work with the fact that the character after between : and ] are always 8 character? – testermaster May 18 '13 at 22:15
  • I was able to reproduce the problem, but still have no idea what causes it. It becomes interesting. – PM 77-1 May 18 '13 at 23:14
  • Solved it. The problem was in a `WHERE` clause: it was working only on strings that start with `[` and end with `]`. Will post the update shortly. – PM 77-1 May 18 '13 at 23:41
  • THANK YOU, you're my legend! Now I've a problem with charset :( http://stackoverflow.com/questions/16634577/mysql-from-utf8-bin-to-latin1-swedish-ci – testermaster May 19 '13 at 12:19