CODEWITHSUNDEEP
mysqlphpmyadmin
user129887
user129887

Reputation:

Replace part of URL only if doesn't contain a given part

I need to replace all URLs containing 31.media.tumblr.com with 41.media.tumblr.com. But if URL 31.media.tumblr.com has .gif in it, it must not get changed.

here's my code;, but it changes everything even URLs with .gif in them... when I run it:

UPDATE wp_posts 
    SET post_content 
      = REPLACE(post_content
               ,'31.media.tumblr.com/'
               ,'41.media.tumblr.com/') 
    WHERE post_content LIKE '%31.media.tumblr.com/%' 
      AND post_content NOT LIKE '%.gif';

p.s. here's an example of 31. url with gif at the end

https://31.media.tumblr.com/79db8c1df4f893bc20d53063e03834f6/tumblr_n1e93kMh3z1rjbi2lo1_500.gif

Upvotes: 2

Views: 86

Answers (2)

gxt
gxt

Reputation: 136

That example URL, you provided, is an image, so I suppose that it is inside something like a <img> tag, if so.. all you need to do is to add % wildcard at the end of .gif. That's why the script isn't working, probably.

try this

UPDATE wp_posts SET post_content = REPLACE(post_content,'31.media.tumblr.com/','41.media.tumblr.com/')
WHERE post_content LIKE '%31.media.tumblr.com/%' 
AND post_content NOT LIKE '%.gif%';

Upvotes: 0

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7181

Your query looks fine, I assume the problem is trailing spaces in post_content. Try:

UPDATE wp_posts 
    SET post_content 
      = REPLACE(post_content
               ,'31.media.tumblr.com/'
               ,'41.media.tumblr.com/') 
WHERE post_content LIKE '%31.media.tumblr.com/%' 
  AND rtrim(post_content) NOT LIKE '%.gif';

and see if that helps. Test case:

create table wp_posts (post_content varchar(255));
insert into wp_posts 
values ('https://31.media.tumblr.com/79db8c1df4f893bc20d53063e03834f6/tumblr_n1e93kMh3z1rjbi2lo1_500.gif')
insert into wp_posts 
values ('https://31.media.tumblr.com/79db8c1df4f893bc20d53063e03834f6/tumblr_n1e93kMh3z1rjbi2lo1_500.gif ');

select * from wp_posts 
where post_content LIKE '%31.media.tumblr.com/%' 
  AND post_content NOT LIKE '%.gif';
[...]
1 row in set (0.00 sec)

select * from wp_posts 
where post_content LIKE '%31.media.tumblr.com/%' 
  AND rtrim(post_content) NOT LIKE '%.gif';
[...]
Empty set (0.00 sec)

Upvotes: 2

Related Questions