Change 1
create table t91 ( id int auto_increment primary key, thing varchar(1000) not null ); insert t91(thing) values ('This is #important# and needs to elaborated further. Remember to buy #milk before coming home#'), ('This is #important# and needs to elaborated further. Remember to buy #milk home#'), ('This is #'); select id,thing,theCount from ( SELECT id,thing, ROUND ( ( LENGTH(thing) - LENGTH( REPLACE ( thing, "#", "") ) ) / LENGTH("#") ) AS theCount FROM t91 ) d where d.theCount>1
The edit above used a strategy that you could take advantage of. This answer
Edit2
-- truncate table t91; create table t91 ( id int auto_increment primary key, thing varchar(1000) not null ); insert t91(thing) values ('This is #important# and needs to elaborated further. Remember to buy #milk before coming home#'), ('This is #important# and needs to elaborated further. Remember to buy #milk home#'), ('This is #'), ('This is #1# ... #2# ... #');
Functions:
DROP FUNCTION IF EXISTS findInsideHashMarks; DELIMITER $$ CREATE FUNCTION findInsideHashMarks(s VARCHAR(200),segments INT) RETURNS VARCHAR(200) BEGIN DECLARE i,nextPos,i1,i2 INT; DECLARE sOut VARCHAR(200); SET i=0; SET nextPos=1; SET sOut=''; WHILE i<segments DO -- LOCATE(substr,str,pos) SET i1=LOCATE('#',s,nextPos); IF i1>0 THEN SET i1=i1+1; SET nextPos=i1+1; SET i2=LOCATE('#',s,nextPos); IF i2>0 THEN SET nextPos=i2+1; SET i2=i2-1; SET sOut=CONCAT(sOut,SUBSTRING(s,i1,i2-i1+1)); END IF; END IF; SET i=i+1; IF i<segments THEN SET sOut=CONCAT(sOut,','); END IF; END WHILE; RETURN sOut; END$$ DELIMITER ;
Query:
SELECT id, SUBSTRING_INDEX(SUBSTRING_INDEX(segString, ',', n.digit+1), ',', -1) dummy FROM ( select id,thing,theCount,cast(floor(theCount / 2) as unsigned) as segments, findInsideHashMarks(thing,cast(floor(theCount / 2) as unsigned)) as segString FROM ( SELECT id,thing, ROUND ( ( LENGTH(thing) - LENGTH( REPLACE ( thing, "#", "") ) ) / LENGTH("#") ) AS theCount FROM t91 ) d1 where d1.theCount>1 ) d2 INNER JOIN (SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) n ON LENGTH(REPLACE(d2.segString, ',' , '')) <= LENGTH(d2.segString)-n.digit ORDER BY d2.id,n.digit;
Output:
+----+-------------------------+ | id | dummy | +----+-------------------------+ | 1 | important | | 1 | milk before coming home | | 2 | important | | 2 | milk home | | 4 | 1 | | 4 | 2 | +----+-------------------------+
The break of the lines of the set (a, b, c) into the lines was inspired (understatement) to this Reply from the user fthiella . As noted in this answer from fthiella (or not explicitly stated), the UNION strategy can be extended to collect, say, 10 or more pieces of data between # markers.
Also note the previous attribution at the bottom of Edit1.
Edit3
Disabling the function and query of Edit2, but using a constant table of helpers to avoid UNION in the query for view n . It supports up to 100 # segments. Uses the previous table (Edit2) t91 and function.
Scheme:
CREATE TABLE 4kTable ( -- a helper table of about 4k consecutive ints id int auto_increment primary key, thing int null )engine=MyISAM; insert 4kTable (thing) values (null),(null),(null),(null),(null),(null),(null),(null),(null); insert 4kTable (thing) select thing from 4kTable; insert 4kTable (thing) select thing from 4kTable; insert 4kTable (thing) select thing from 4kTable; insert 4kTable (thing) select thing from 4kTable; insert 4kTable (thing) select thing from 4kTable; insert 4kTable (thing) select thing from 4kTable; insert 4kTable (thing) select thing from 4kTable; insert 4kTable (thing) select thing from 4kTable; insert 4kTable (thing) select thing from 4kTable; -- verify: -- select min(id),max(id),count(*) from 4kTable; -- 1 4608 4608 ALTER TABLE 4kTable ENGINE = InnoDB; -- *********** it is now InnoDB -- verify: -- select min(id),max(id),count(*) from 4kTable; -- 1 4608 4608 -- no innodb auto_increment gaps (consecutive block)
Query:
SELECT id, SUBSTRING_INDEX(SUBSTRING_INDEX(segString, ',', n.digit+1), ',', -1) dummy FROM ( select d1.id,thing,theCount,cast(floor(theCount / 2) as unsigned) as segments, findInsideHashMarks(thing,cast(floor(theCount / 2) as unsigned)) as segString FROM ( SELECT id,thing, ROUND ( ( LENGTH(thing) - LENGTH( REPLACE ( thing, "#", "") ) ) / LENGTH("#") ) AS theCount FROM t91 ) d1 where d1.theCount>1 ) d2 INNER JOIN (select id-1 as digit from 4kTable where id<=100) n ON LENGTH(REPLACE(d2.segString, ',' , '')) <= LENGTH(d2.segString)-n.digit ORDER BY d2.id,n.digit;
The same output as in the Edit2 Output section. Usually I have some persistent helper tables in the system, especially for left joins with dates.