This may come across as silly, but if the CMS ignores subsequent <!--more-->
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).
I used the following, which gets you part of the way there.
USE tempdb;
GO
CREATE TABLE Test
(
uname VARCHAR(255)
, num INT
);
INSERT INTO Test (uname, num) VALUES ('Max', 1);
INSERT INTO Test (uname, num) VALUES ('Max', 2);
INSERT INTO Test (uname, num) VALUES ('Max', 3);
INSERT INTO Test (uname, num) VALUES ('Max', 4);
INSERT INTO Test (uname, num) VALUES ('Max', 5);
INSERT INTO Test (uname, num) VALUES ('Max', 11);
INSERT INTO Test (uname, num) VALUES ('Max', 12);
INSERT INTO Test (uname, num) VALUES ('Max', 13);
INSERT INTO Test (uname, num) VALUES ('Max', 14);
INSERT INTO Test (uname, num) VALUES ('Max', 15);
INSERT INTO Test (uname, num) VALUES ('Jen', 22);
INSERT INTO Test (uname, num) VALUES ('Jen', 23);
INSERT INTO Test (uname, num) VALUES ('Jen', 24);
INSERT INTO Test (uname, num) VALUES ('Jen', 25);
SELECT uname
, CAST(min(num) as varchar(255)) + ' - ' + CAST(max(num) as varchar(255)) AS NumRange
FROM (
SELECT uname, num - ROW_NUMBER() OVER (PARTITION BY uname ORDER BY num) groupnum, num
FROM dbo.Test
) t
GROUP BY uname, groupnum;
Results:
Here is the sample code provided so kindly by @AaronBertrand
;WITH cte as (
SELECT uname
, min(num) As MinNum
, CAST(min(num) as varchar(255)) + ' - ' + CAST(max(num) as varchar(255)) AS NumRange
FROM (
SELECT uname, num - ROW_NUMBER() OVER (PARTITION BY uname ORDER BY num) groupnum, num
FROM dbo.Test
) t
GROUP BY uname, groupnum
)
SELECT DISTINCT uname
, STUFF((
(
SELECT ', ' + NumRange
FROM cte as cte1
WHERE cte.uname = cte1.uname
ORDER BY cte1.MinNum
FOR XML PATH, TYPE
).value('.[1]','VARCHAR(255)')
), 1, 2, '') as NumRange
FROM cte;
The results:
STUFF
is used here to remove the leading ,
from the results. It does that by replacing the first two leading characters in the concatenated NumRange with a zero-length string ''
.
Best Answer
Given your test data, this will parse out the section of the string you want between either the first semi-colon after the
PATH=
, or the end of the string if that doesn't exist. From here, you should be able to use an update to replace that section of the string.Further reading: Get The Text Between Two Delimiters