Replace part of a string in MYSQL, when only the substring's starting characters, ending characters & a unique string in the middle is known?

Question

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?

Answer 1

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

• Josh979: 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"
• Akina: @Josh979 One bracket was omitted... you could easily fix it yourself.
• Josh979: 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.
• Akina: @Josh979 This is written "by hands, on the fly" - of course misprints possible. Sorry... Lost bracked added.