Mysql – Need to replace only first occurrence of string

MySQLreplace

I hope I worded the question right. Here are the specifics:

This is regarding a MySQL database. I've inherited several hundred posts with custom fields and a unique excerpt. At this point I think I can solve the problem by inserting the  tag into each post at a specific point. I need to do a search and replace with the following parameters:

Search each post_content field for the first occurrence of 
Insert the  tag right after the

Unfortunately each post has multiple  tag pairs, and if a post has more than one  tag in it, WordPress just ignores all of them. So I need to so some sort of search and replace, but I have had no success after two days of trying every possible code snippet, SQL query and plugin I could find.

Best Answer

This may come across as silly, but if the CMS ignores subsequent  tags, you shouldn't really care if it adds one after every  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  tag (and you may need to define the requirements for what to do in those cases).

Related Solutions

Mysql – Replace a specific part of a string

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.

Postgresql – regexp_replace() vs. replace() to replace all matches in a string

Why?

Because Postgres replace() is a standard SQL function that works the same as in other RDBMS. Example: replace() in SQL Server:

Replaces all occurrences of a specified string value with another string value.

While regexp_replace() is used to ...

Replace substring(s) matching a POSIX regular expression.

The handling of regular expressions is obviously guided by the POSIX standard and works the same as other tools implementing it. The manual:

POSIX regular expressions provide a more powerful means for pattern matching than the LIKE and SIMILAR TO operators. Many Unix tools such as egrep, sed, or awk use a pattern matching language that is similar to the one described here.

Best Answer

Related Solutions

Mysql – Replace a specific part of a string

Postgresql – regexp_replace() vs. replace() to replace all matches in a string

Why?

Related Question