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

concatMySQLreplace

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?

Best Answer

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

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 unique index only if column has specific value

You don't have to check what value you could use in "deleted" if you have a way of generating unique integers, and MySQL does... the UUID_SHORT() function generates a unique and monotonic BIGINT UNSIGNED value every time it's called.

The function is good for 16.7M new values per second, every second, and will never return a duplicate (or smaller than previous) value as long as the @@SERVER_ID remains the same and you don't shut down the server, set the system clock backward in time, and restart the server... and even if you do, the odds of duplicate values are still very small, but no longer truly impossible.

You could also make "deleted" be a DATETIME(6) or TIMESTAMP(6) on MySQL 5.6, using '0000-00-00 00:00:00.000000' to mean "not deleted" and NOW() to mean deleted.

Try as I might, I have not been able to get MySQL to produce a NOW() collision down to the microsecond.

Best Answer

Related Solutions

Mysql – Need to replace only first occurrence of string

MySQL unique index only if column has specific value

Related Question