0

I have some string which I need to replace in my database using a SQL query. The string is:

[attachment=NUMBER]image.jpg[/attacment]

I want to wrap that image.jpg with an HTML image tag, but the NUMBER is screwing me up because it will be different for each string. Is there a wildcard character I can use in this query which will find any number?

Here's my search/replace query:

UPDATE `hd_posts`
SET `post_content` = 
       replace(post_content, 
               '[attachment=NUMBER]', 
               '<img src="http://mydomain.com/my-theme/images/')

and then...

UPDATE `hd_posts`
 SET `post_content` = replace(post_content, '[/attachment]', '" />')

...so that I will end up with this:

<img src="http://mydomain.com/my-theme/images/image.jpg" />

Can anyone help? What do I use for NUMBER? I have a feeling that this is not too difficult, but I am a complete novice at this. Of course I've searched and searched, but I can't find this exact situation anywhere from which to learn. These two almost gave me what I need:

MySQL REPLACE variable string Search & replace in MySql, using regex

but not quite. I'm still confused! Thanks for any help.

If I use that first link as an example...

UPDATE `hd_posts` 
  SET `post_content` = 
         CONCAT(SUBSTRING(post_content, 1, 
                          INSTR(post_content,'[attachment') - 1),
                SUBSTRING(post_content, 
                          INSTR(post_content, ']'), 
                          LENGTH(post_content) 
                            - INSTR(post_content, '[attachment'))
                )

I have no idea...in that example he was trying to replace a string with '' (remove it), but I don't see how he is doing that, and I don't see how I can learn from it enough to do what I need to do.

Improve this question
8
  • You have a right answer at the first link for your requirement too. Commented Apr 5, 2014 at 17:04
  • So, should I do this: UPDATE hd_posts SET post_content = CONCAT(SUBSTRING(post_content, 1, INSTR(post_content,'[attachment') - 1),SUBSTRING(post_content , INSTR(post_content, ']'), LENGTH(post_content) - INSTR(post_content, '&add_id'))) I'm afraid to try, but would that work? It's a HUGE database so it will be a major hassle to restore from backup if I mess up. Commented Apr 5, 2014 at 17:12
  • No, that wouldn't work, because there are two closing brackets: ']' Commented Apr 5, 2014 at 17:17
  • @Ravinder I forgot to replace '&add_id' with '[attachment' in that comment above, but I would appreciate any help you could give me here. Commented Apr 5, 2014 at 17:19
  • What is the exact string format you have? Post it in the question. Not in the comments, please. Commented Apr 5, 2014 at 17:20

1 Answer 1

Reset to default
0

[attachment=NUMBER]image.jpg[/attacment].

There is a typo in [/attacment]. Letter h was missing.
Change it to         [/attachment]

If the format of content is always this, then 

set @pc := '[attachment=NUMBER]image.jpg[/attachment]';
set @instr := INSTR(@pc, ']');
set @start_tag := substring(@pc, 1, @instr);
set @end_tag := '[/attachment]';
set @image_url := 
  replace(
    replace( @pc, @start_tag, 
             '<img src="http://mydomain.com/my-theme/images/'
    ), @end_tag, '" />');

select @pc;
+-------------------------------------------+
| @pc                                       |
+-------------------------------------------+
| [attachment=NUMBER]image.jpg[/attachment] |
+-------------------------------------------+

select @instr, @start_tag, @end_tag;
+--------+---------------------+---------------+
| @instr | @start_tag          | @end_tag      |
+--------+---------------------+---------------+
|     19 | [attachment=NUMBER] | [/attachment] |
+--------+---------------------+---------------+

select @image_url;
+-------------------------------------------------------------+
| @image_url                                                  |
+-------------------------------------------------------------+
| <img src="http://mydomain.com/my-theme/images/image.jpg" /> |
+-------------------------------------------------------------+

Demo @ SQL Fiddle

And your update statement can be: 

UPDATE hd_posts
SET post_content = 
       replace(
        replace( @pc:=post_content, substring( @pc, 1, INSTR( @pc, ']' ) ), 
                 '<img src="http://mydomain.com/my-theme/images/'
        ), '[/attachment]', '" />' )
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Wow, you rock! That was a lot more complicated than I was expecting. I can't thank you enough for your expertise. Works perfectly.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.