Mysql – Replace a specific part of a string

MySQLreplaceupdate

I have many strings in the database like:

<iframe src="https://www.youtube.com/embed/-qTiYA1WiY8&index=8&list=PLPNcLSfYNC3Dw535smncyHRCBRmdf1Bti"
    frameborder="0" allowfullscreen></iframe>

Original

www.youtube.com/embed/-qTiYA1WiY8&index=8&list=PLPNcLSfYNC3Dw535smncyHRCBRmdf1Bti

Desired

www.youtube.com/embed/-qTiYA1WiY8

So final string can be converted to:

<iframe src="https://www.youtube.com/embed/-qTiYA1WiY8"
    frameborder="0" allowfullscreen></iframe>

I can do this using PHP, but I want to do it with a MySQL query. I tried to use:

UPDATE MyTable    
    SET StringColumn = REPLACE (StringColumn, 'SearchForThis', 'ReplaceWithThis')

It needs to be a general solution that can replace string from & and before " if exist in the string. I am not able to get the proper result.

Best Answer

If the strings are all the same, and the column is called c1 in a table called t1 then SUBSTRING_INDEX() might get you there. Don't update your table until you've verified it with SELECT.

Whitespace added for clarity.

SELECT CONCAT(
  SUBSTRING_INDEX(c1,'&',1),
  '"',
  SUBSTRING_INDEX(c1,'"',-3)
) FROM t1;

SELECT the substring left of the first '&' counting from the left, a literal " and then the substring right of the third " counting from the right.

The only caveat is that the link format you've shown seems wrong. The first "&" seems like it should have been "?" instead, and you should be clipping it at the "?" which marks the start of the query string in a URL. Perhaps youtube was doing something nonstandard with URLs to work around plugin or browser issues.

Related Solutions

Mysql – Need to replace only first occurrence of string

This may come across as silly, but if the CMS ignores subsequent  tags, you shouldn't really care if it adds one after every </em> closing tag. Sure it may make the posts slightly larger than they should be, but since they're just going to be ignored, it seems silly to spend two days trying to not do that when at the end of the day it doesn't really matter whether you do or not.

That said, I am not a MySQL guy, but looks like this is as decent an approach as any, from this StackOverflow answer:

UPDATE wp_posts
 SET post_content = CONCAT(REPLACE(LEFT(post_content, 
   INSTR(post_content, '</em>')+4), '</em>', '</em><!--more-->'), 
   SUBSTRING(post_content, INSTR(post_content, '</em>') + 5))
 WHERE INSTR(post_content, '</em>') > 0;

or the (slightly simpler):

UPDATE wp_posts
 SET post_content = CONCAT(LEFT(post_content, INSTR(post_content, '</em>')-1),
                           '</em><!--more-->',
                           SUBSTRING(post_content, INSTR(post_content, '</em>')+ 5))
 WHERE INSTR(post_content, '</em>') > 0;

Before you do that you may want to check to see how many rows this will affect:

SELECT COUNT(*)
  FROM wp_posts
  WHERE INSTR(post_content, '</em>') > 0;

You'll probably want to add a WHERE clause to only identify those posts that actually contain an </em> tag (and you may need to define the requirements for what to do in those cases).

Mysql – Create multiple columns from a single column filtering criteria

I read the chapter Generating Summaries in MySQL Cookbook, this is where they explain that you can create count, sum and other methods inside your query to gain the proper values in those columns you define, this is known as statistical queries and then this is the query I ended up using:

SELECT user_id as userid, count(id) Total,
COUNT(IF(revision = 'pending', 1, NULL)) as pending,
COUNT(IF(revision = 'approved', 1, NULL)) as approved,
COUNT(IF(revision = 'rejected', 1, NULL)) as rejected
FROM table1
GROUP BY user_id

This will fill those values by count only if criteria is met.