0

I'm trying to find a way to replace part of a string in a database without knowing the exact substring content other than how it starts, ends and has a specific ID string somewhere in between that.

To specify, I have Vimeo iframes that are within post content inside a column in a MySQL database table. I need to replace all vimeo iframes strings in this column over to another video platform script. I have a list to use containing the Vimeo IDs and the new code to go in place of the existing Vimeo iframe, so this gives me a unique identifier to use when searching.

The problem is, some of the Vimeo urls have additional GET queries added to the URL so I can't do a 1 for 1 match of the Vimeo iframe.

I do know for certain that each iframe instance I need to replace will begin with <iframe, end with </iframe> and contain a specific Vimeo ID (ie 1234567). Is there a way to do this with Regex and concat or something similar so that I can replace the entire iframe code with a new string?

0

Test:

UPDATE source_table
SET field = CONCAT( SUBSTRING_INDEX(field, @start, 1),
                    @replacement,
                    SUBSTRING_INDEX(field, @end, -1)
                  )
WHERE LOCATE(@middle, SUBSTRING_INDEX(SUBSTRING_INDEX(field, @start, -1), @end, 1))
| improve this answer | |
  • Thanks for your response. I get the following error when I try to run this: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE LOCATE(@middle, SUBSTRING_INDEX(SUBSTRING_INDEX(post_content, @start, -1),' at line 5" – Josh979 Jan 27 at 18:14
  • @Josh979 One bracket was omitted... you could easily fix it yourself. – Akina Jan 27 at 18:18
  • Actually, there were two closing parenthesis missing. one from CONCAT and one at the end of LOCATE (which you already updated). I caught the first one, I didn't initially catch the second one. Thanks for your help. – Josh979 Jan 27 at 18:55
  • @Josh979 This is written "by hands, on the fly" - of course misprints possible. Sorry... Lost bracked added. – Akina Jan 27 at 19:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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