0

i have this text:

<p>[img ret872154ftu] fileaddress [/img ret872154ftu]</p>
<p>[img fd68721cvn] fileaddress [/img fd68721cvn]</p>
<p>[img xdfh654t] fileaddress [/img xdfh654t]</p>

i like change to this

<p>[img] fileaddress [/img]</p>
<p>[img] fileaddress [/img]</p>
<p>[img] fileaddress [/img]</p>

this code is tested but not worked

 UPDATE `table` SET `name` = replace(`name`, '[img (anything)', '[/img]');

please help

Maziar Moradpour
  • 3
  • 2
  • look at this similar question http://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql – amaster Jan 01 '14 at 18:21

1 Answers1

2

If you always have a space before and after the fileaddress, this will extract the fileaddress part:

SUBSTRING_INDEX(
  SUBSTRING_INDEX(name, '] ', -1),
  ' [', 1);

and you can then use this update query:

UPDATE `table`
SET `name` = CONCAT(
               '<p>[img] ',
               SUBSTRING_INDEX(
                 SUBSTRING_INDEX(name, '] ', -1),
                 ' [', 1),
               ' [/img]</p>');

Please see fiddle here.

Edit: unfortunately, MySQL support for regular expressions is very limited. You could try with this quick and dirty solution, there are multiple circumstances in which it could fail, but it might work:

UPDATE tablename
SET
  name = 
  CONCAT(
    SUBSTRING_INDEX(name, '[img', 1),
    '[img]',
   SUBSTRING_INDEX(
    SUBSTRING_INDEX(
      SUBSTRING_INDEX(name, '[img', -1),
      '[/img',
      1),
    ']',
    -1),
    '[/img]',
    SUBSTRING_INDEX(name, ']', -1)
  )

Fiddle is here.

fthiella
  • 48,073
  • 15
  • 90
  • 106
  • The big problem this Field name is post_content on wp_posts for Wordpress and not only previous code test test test[img xdfh654t]http://fb.com/images/63636383481679564011.jpg[/img xdfh654t] test test test please help me – Maziar Moradpour Jan 01 '14 at 19:34