Replace a specific part of a string

Question

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.

• Rick James: MariaDB has REGEXP_REPLACE; MySQL does not.

Answer 1

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.

Answer 2

One approach may be:

Run query with two conditions:

1. Get the substring out of the string that you want to replace with blank characters. Example:

   SELECT RIGHT(StringColumn,LOCATE('&',StringColumn))
   

   This will return the characters to the right of the '&' .

2. Merge this with the REPLACE function. Example:

   UPDATE MyTable 
   SET StringColumn = REPLACE (StringColumn, (
    SELECT RIGHT(StringColumn,LOCATE('&',StringColumn))) , '')