0

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.

1
  • 1
    MariaDB has REGEXP_REPLACE; MySQL does not.
    – Rick James
    Commented Sep 16, 2015 at 12:57

2 Answers 2

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.

0

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))) , '')
    

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.