A MySQL recipe, that you can use to split a cell value by a known separator into different rows, in some way similar to the PHP explode function or split in PERL.
To turn this:
| id | value |
|---|---|
| 1 | 4,5,7 |
| 2 | 4,5 |
| 3 | 4,5,6 |
| …. | …. |
Into this
| id | value |
|---|---|
| 1 | 4 |
| 1 | 5 |
| 1 | 7 |
| 2 | 4 |
| 2 | 5 |
| 3 | 4 |
| 3 | 5 |
| 3 | 6 |
| …. | …. |
You can simply write and call a stored procedure
DELIMITER $$
DROP PROCEDURE IF EXISTS explode_table $$
CREATE PROCEDURE explode_table(bound VARCHAR(255))
BEGIN
DECLARE id INT DEFAULT 0;
DECLARE value TEXT;
DECLARE occurance INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE splitted_value INT;
DECLARE done INT DEFAULT 0;
DECLARE cur1 CURSOR FOR SELECT table1.id, table1.value
FROM table1
WHERE table1.value != '';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
DROP TEMPORARY TABLE IF EXISTS table2;
CREATE TEMPORARY TABLE table2(
`id` INT NOT NULL,
`value` VARCHAR(255) NOT NULL
) ENGINE=Memory;
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO id, value;
IF done THEN
LEAVE read_loop;
END IF;
SET occurance = (SELECT LENGTH(value)
- LENGTH(REPLACE(value, bound, ''))
+1);
SET i=1;
WHILE i <= occurance DO
SET splitted_value =
(SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(value, bound, i),
LENGTH(SUBSTRING_INDEX(value, bound, i - 1)) + 1), ',', ''));
INSERT INTO table2 VALUES (id, splitted_value);
SET i = i + 1;
END WHILE;
END LOOP;
SELECT * FROM table2;
CLOSE cur1;
END; $$
Then you simply call it
CALL explode_table(',');
There it is the bare bones. From here it’s simple to adapt and build to your own needs, like adding some kind of filter parameter, order, etc… if your main interface to Mysql is PHPMyAdmin (as of now) forget it, its rubish with this procedures queries, you can use own MySQL GUI – MySQL Workbench – to interface with, or rely on the old CLI ‘mysql’ command, just put the stored procedure definition in a file and load it with a redirect:
mysql -u username -p -D databasename < procedure_definition_file.txt
Also remember:
- if backups are made with mysqldump, use the –routines switch so the stored procedure definition goes in the dumps.
- works mysql >= 5.0 only
- performance, normalization and concurrency – this is not the correct way to do a many to many relationship with a RDBS, you should use a relationship table, and joins to work with it.
- OK, so your project manager/marketing/boss changed the game rules at the very last moment, and to implement it correctly you must rework a lot of code, i understand 🙂 but even then enter this road at your own peril.
It`s a nice funktion, but how to store values in additional fields with one id.
Like this:
id value1 value2 value3
1 4 5 7
2 4 5
3 4 5 6
@Tobias
if you have this kind of table layout, you know in advance the number of field values, so i think you just need a SQL union query.
This is the SQL definition of your example:
CREATE TABLE table1 (
id INT NOT NULL ,
value1 INT NULL ,
value2 INT NULL ,
value3 INT NULL ,
PRIMARY KEY ( `id` )
);
INSERT INTO table1 VALUES ('1', '4', '5', '7');
INSERT INTO table1 VALUES ('2', '4', '5', null);
INSERT INTO table1 VALUES ('3', '4', '5', '6');
And the union query (i filtered out all the null values with a sub-query) as:
SELECT * FROM (
SELECT id, value1 AS value FROM table1
UNION
SELECT id, value2 AS value FROM table1
UNION
SELECT id, value3 AS value FROM table1
ORDER BY id) AS t1
WHERE value IS NOT NULL
Can you explain to me, what `bound` does?
@Tobias
“bound” is abbreviation for boundary (as in value boundary), it’s the char(s) that you want to explode by. In my example i have the values split by commas, as in “1,2,3” but i could have another char (ex: a pipe) “1|2|3″ and the function works fine, just call it with the correct delimiter:
CALL explode_table(‘,’); // for comma separated values
CALL explode_table(‘|’); // for pipe separated values
etc…
ps – just didn’t use”delimiter” because it’s a SQL word.
For a better understanding, please take a look at this:
I try to split the values from `datafield`:
VALUES:
88044,A,56
Therefore i wrote this:
BEGIN
DECLARE id INT DEFAULT 0;
DECLARE value varchar(64);
DECLARE occurance INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE splitted_value varchar(64);
DECLARE done INT DEFAULT 0;
DECLARE cur1 CURSOR FOR SELECT interface.id, interface.datafield
FROM interface
WHERE interface.datafield != ”;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
DROP TABLE if exists OrderTask;
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO id, value;
IF done THEN
LEAVE read_loop;
END IF;
SET occurance = (SELECT LENGTH(value)
– LENGTH(REPLACE(value, bound, ”))
+1);
SET i=1;
WHILE i <= occurance DO
SET splitted_value =
(SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(value, bound, i),
LENGTH(SUBSTRING_INDEX(value, bound, i – 1)) + 1 ) ,',', ''));
INSERT INTO OrderSubstr(interface_id, order_id) VALUES (id, splitted_value);
SET i = i + 1;
END WHILE;
END LOOP;
Create Table OrderTask AS
(SELECT OrderSubstr.interface_id, OrderSubstr.order_id, OrderSubstr.order_status, OrderSubstr.order_total
FROM OrderSubstr LEFT JOIN interface ON interface.id = OrderSubstr.interface_id
WHERE interface.status = '-' AND interface.acceptor = 'erp' AND interface.actiontype = 2);
DELETE FROM OrderSubstr WHERE id is not NULL;
CLOSE cur1;
END;
The Result is:
interface_id | order_id | order_status | order_total 20533949 | 88044 | NULL | NULL
20533949 | A | NULL | NULL
20533949 | 56 | NULL | NULL
My problem is to split data into one row. The result should be:
interface_id | order_id | order_status | order_total 20533949 | 88044 | A | 56
Splitting in one row by using the loop, thats it!
@Tobias
can you put here the original tables (interface) with some data?
Thanks for your good explanation and example. Its saves my tones n tones of time. I have more columns instead two columns. Lets take 5 columns with multiple values in each Row. I wanna a output like a combination. Is it possible??? Can u guide me???
@Bala
just use CONCAT or CONCAT_WS to work out the n columns into 2, ex:
SELECT id, CONCAT(col_1, col_2, col_3, col_4) AS values FROM table 1
Thanks for sharing this. Saved me quite some time.
I had some problems with UTF-8 / latin1 charater mixery issues in my use case. The LENGTH() – Command was calculating a wrong length if there was an ‘ü’ or ß or some other special character in the string.
So I changed it to CHAR_LENGTH() which gives you back the real length of a string even with special characters and encoding issues inside ist.
If someone runs into problems with this script that words get chopped of than give this CHAR_LENGTH() a try.
I solved the problem with the help of your ideas. Thanks a lot. I use a case statement while doing the loop. Now the script takes every value and splits it by reading pipes into several fields.
DELIMITER $$
DROP PROCEDURE IF EXISTS customers $$
CREATE PROCEDURE customers(bound VARCHAR(255))
BEGIN
DECLARE id INT DEFAULT 0;
DECLARE value TEXT;
DECLARE occurance INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE splitted_value varchar(64);
DECLARE done INT DEFAULT 0;
DECLARE cur1 CURSOR FOR SELECT ERP.id, ERP.datafield
FROM ERP
WHERE ERP.datafield != ” AND actiontype = 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
— DELETE FROM CustomerTask WHERE interface_id is not NULL;
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO id, value;
IF done THEN
LEAVE read_loop;
END IF;
SET occurance = (SELECT LENGTH(value)
– LENGTH(REPLACE(value, bound, ”))
+1);
SET i=1;
WHILE i <= occurance DO
SET splitted_value =
(SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(value, bound, i),
LENGTH(SUBSTRING_INDEX(value, bound, i – 1)) + 1), '|', ''));
CASE i
WHEN 1 THEN
INSERT INTO SplitCustomers(interface_id, user_info_id) VALUES (id, splitted_value);
WHEN 2 THEN
UPDATE SplitCustomers SET user_id = splitted_value WHERE interface_id = id;
WHEN 3 THEN
UPDATE SplitCustomers SET address_type = splitted_value WHERE interface_id = id;
WHEN 4 THEN
UPDATE SplitCustomers SET company = splitted_value WHERE interface_id = id;
WHEN 5 THEN
UPDATE SplitCustomers SET title = splitted_value WHERE interface_id = id;
WHEN 6 THEN
UPDATE SplitCustomers SET last_name = splitted_value WHERE interface_id = id;
WHEN 7 THEN
UPDATE SplitCustomers SET first_name = splitted_value WHERE interface_id = id;
WHEN 8 THEN
UPDATE SplitCustomers SET phone_1 = splitted_value WHERE interface_id = id;
WHEN 9 THEN
UPDATE SplitCustomers SET phone_2 = splitted_value WHERE interface_id = id;
WHEN 10 THEN
UPDATE SplitCustomers SET fax = splitted_value WHERE interface_id = id;
WHEN 11 THEN
UPDATE SplitCustomers SET address_1 = splitted_value WHERE interface_id = id;
WHEN 12 THEN
UPDATE SplitCustomers SET address_2 = splitted_value WHERE interface_id = id;
WHEN 13 THEN
UPDATE SplitCustomers SET city = splitted_value WHERE interface_id = id;
WHEN 14 THEN
UPDATE SplitCustomers SET state = splitted_value WHERE interface_id = id;
WHEN 15 THEN
UPDATE SplitCustomers SET country = splitted_value WHERE interface_id = id;
WHEN 16 THEN
UPDATE SplitCustomers SET zip = splitted_value WHERE interface_id = id;
WHEN 17 THEN
UPDATE SplitCustomers SET user_email = splitted_value WHERE interface_id = id;
WHEN 18 THEN
UPDATE SplitCustomers SET extra_field_1 = splitted_value WHERE interface_id = id;
WHEN 19 THEN
UPDATE SplitCustomers SET extra_field_2 = splitted_value WHERE interface_id = id;
WHEN 20 THEN
UPDATE SplitCustomers SET extra_field_3 = splitted_value WHERE interface_id = id;
END CASE;
SET i = i + 1;
CLOSE cur1;
END;
The next question is, how to do it the other way? Taking several fields and write it
into one Field. Any ideas?
One that I’m pondering on at the moment is a variance on the other way around.
I’m updating a system that I have inherited and it has type fields to describe properties eg: type1=”with pool”, type2=”near beach”, type3=”close to shops” etc…
I have normalized this into table types and property_type join table:
id int(11) ~ auto-increment
property_id int(11) ~ FK to property table
type_id int(11) ~ FK to type table
I thought I would be able to use a simple CASE statement thus:
SELECT Q.prop_id,
MAX(IF(Q.type = 20,’Y’,’N’)) AS ‘with pool’,
MAX(IF(Q.type = 21,’Y’,’N’)) AS ‘near beach’,
MAX(IF(Q.type = 22,’Y’,’N’)) AS ‘close to shops’,
FROM test Q
GROUP BY Q.prop_id
BUT there are more types in the database than there are type columns.
I want a pure SQL solution as I don’t want to change the website front-end …
Perhaps, it’s time to wrtie a stored procedure …
…any ideas ???
In the example od tutorial you have to change in the DECLARE:
DECLARE splitted_value INT;
by
DECLARE splitted_value TEXT;
Regards.
Good point there.
Here in the example we are working with integers thus the DECLARE splitted_value INT;
But of course if you want to split text, you should declare it as TEXT.
My goal was something next of your example, so i have done some changes to fit with my needs, so maybe it can also help someone… It just expects as paramter the string, and a separator character… then it returns the recordset, resuming, it split column into rows…
Thanks,
ewil
THE CODE:
DELIMITER $$
DROP PROCEDURE IF EXISTS explodeintotable $$
CREATE PROCEDURE explodeintotable(str VARCHAR(255), sep VARCHAR(1))
BEGIN
DECLARE occurance INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE splitted_value VARCHAR(100);
DROP TEMPORARY TABLE IF EXISTS table2;
CREATE TEMPORARY TABLE table2(
`vl` VARCHAR(255) NOT NULL
) ENGINE=Memory;
/* QUANTAS OCORRENCIAS TEM */
SET occurance = (SELECT LENGTH(str)
– LENGTH(REPLACE(str, sep, ”))
+1);
SET i=1;
WHILE i <= occurance DO
SET splitted_value =
(SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(str, sep, i),
LENGTH(SUBSTRING_INDEX(str, sep, i – 1)) + 1), sep, ''));
INSERT INTO table2 VALUES (splitted_value);
SET i = i + 1;
END WHILE;
SELECT * FROM table2;
END; $$
Very helpful. Thank you
have the following database:
id | user | grades
1 | 1 | 08-09-10; 07-06-08; 05-04-07
2 | 3 | 10-09-06; 05-04-03…
Is there a way to get this:
id | user | grade1 | grade2 | grade3 |
1 | 1 | 08 | 09 | 10 |
2 | 1 | 07 | 06 | 08 |
3 | 1 | 05 | 04 | 07 |
4 | 3 | 10 | 09 | 06
THx for all help.
Hello Nelson, i think the main problem is that you have to create a temporary table to hold the data based on a variable number of entries (the grades). And you have spaces, semi-colon and dashes to split…. You should really think about redesign your data structure and save yourself a long visit to the SQL house of pain 🙂
SELECT posts FROM today WHERE helpful = true AND clean_framework = true;
1row(s) returned
Thanks,
For some reason this procedure doesn’t work for me. Can you help me out?
This is what I wrote and changed.
DELIMITER $$
DROP PROCEDURE IF EXISTS explode_table $$
CREATE PROCEDURE explode_table(bound VARCHAR(255))
BEGIN
DECLARE User_ID Text;
DECLARE Level_Date DATETIME;
DECLARE Last_Date_Role DATETIME;
DECLARE Days_In_Role INT DEFAULT 0;
DECLARE Low_Level TEXT;
DECLARE a INT DEFAULT 0;
DECLARE b INT DEFAULT 0;
DECLARE c INT DEFAULT 0;
DECLARE d INT DEFAULT 0;
DECLARE e INT DEFAULT 0;
DECLARE f INT DEFAULT 0;
DECLARE g INT DEFAULT 0;
DECLARE h INT DEFAULT 0;
DECLARE z INT DEFAULT 0;
DECLARE j INT DEFAULT 0;
DECLARE k INT DEFAULT 0;
DECLARE l INT DEFAULT 0;
DECLARE m INT DEFAULT 0;
DECLARE occurance INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE splitted_value INT;
DECLARE done INT DEFAULT 0;
DECLARE cur1 CURSOR FOR SELECT q1.User_ID, Level_Date,Last_Date_Role,Days_In_Role,Low_Level
,sum(case when Class like ‘%a%’ and Rev_Date>Level_Date then 1 else 0 end) as a
,sum(case when Class like ‘%b%’ and Rev_Date>Level_Date then 1 else 0 end) as b
,sum(case when Class like ‘%c%’ and Rev_Date>Level_Date then 1 else 0 end) as c
,sum(case when Class like ‘%d%’ and Rev_Date>Level_Date then 1 else 0 end) as d
,sum(case when Class like ‘%e%’ and Rev_Date>Level_Date then 1 else 0 end) as e
,sum(case when Class like ‘%f%’ and Rev_Date>Level_Date then 1 else 0 end) as f
,sum(case when Class like ‘%g%’ and Rev_Date>Level_Date then 1 else 0 end) as g
,sum(case when Class like ‘%h%’ and Rev_Date>Level_Date then 1 else 0 end) as h
,sum(case when Class like ‘%i%’ and Rev_Date>Level_Date then 1 else 0 end) as z
,sum(case when Class like ‘%j%’ and Rev_Date>Level_Date then 1 else 0 end) as j
,sum(case when Class like ‘%k%’ and Rev_Date>Level_Date then 1 else 0 end) as k
,sum(case when Class like ‘%l%’ and Rev_Date>Level_Date then 1 else 0 end) as l
,sum(case when Class like ‘%m%’ and Rev_Date>Level_Date then 1 else 0 end) as m
from (select * from codna.jmis2012 where Class is not null) q1 inner join users_role_changes
on q1.User_ID like users_role_changes.User_ID
where Last_Date_Role is not null and Low_Level not like ‘%bot%’
group by User_ID,Level_Date,Last_Date_Role,Days_In_Role,Low_Level;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
DROP TEMPORARY TABLE IF EXISTS table2;
CREATE TEMPORARY TABLE table2(
`User_ID` INT NOT NULL,
`Level_Date` DATETIME NOT NULL,
`Last_Date_Role` DATETIME NOT NULL,
`Days_In_Role` DATETIME NOT NULL,
`Low_Level` VARCHAR(255) NOT NULL,
`a` INT NOT NULL,
`b` INT NOT NULL,
`c` INT NOT NULL,
`d` INT NOT NULL,
`e` INT NOT NULL,
`f` INT NOT NULL,
`g` INT NOT NULL,
`h` INT NOT NULL,
`z` INT NOT NULL,
`j` INT NOT NULL,
`k` INT NOT NULL,
`l` INT NOT NULL,
`m` INT NOT NULL
) ENGINE=Memory;
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO User_ID,Level_Date,Last_Date_Role,Days_In_Role,Low_Level,a,b,c,d,e,f,g,h,z,j,k,l,m;
IF done THEN
LEAVE read_loop;
END IF;
SET occurance = (SELECT LENGTH(Low_Level)
– LENGTH(REPLACE(Low_Level, bound, ”))
+1);
SET i=1;
WHILE i <= occurance DO
SET splitted_value =
(SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(Low_Level, bound, i),
LENGTH(SUBSTRING_INDEX(Low_Level, bound, i – 1)) + 1), ',', ''));
INSERT INTO table2 VALUES (User_ID,Level_Date,Last_Date_Role,Days_In_Role,Low_Level,a,b,c,d,e,f,g,h,z,j,k,l,m);
SET i = i + 1;
END WHILE;
END LOOP;
SELECT * FROM table2;
CLOSE cur1;
END; $$
I have a problem i need to build cursor inside SP to take value from xml
here xml i want to read it
1
17
1,22
DUPLICATE DRUG CLASS ORDER
1,22
1
16
2,22
DUPLICATE DRUG ORDER
2,22
2
32
3,22
NO ALLERGY ASSESSMENT
3,22
and the parser doesn’t work to take all Id
BEGIN
DECLARE my_delimiter CHAR(1);
DECLARE v_finished INTEGER DEFAULT 0;
DECLARE v_test varchar(100) DEFAULT “”;
DECLARE occurance INT;
DECLARE i2 INT;
DECLARE ID_cursor CURSOR FOR
SELECT ExtractValue(@OrderOrderCheckXML, ‘/ORDERCHECKSS/Idvalue’) ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;
OPEN ID_cursor;
read_loop: loop
FETCH ID_cursor INTO v_test;
SET @IEN=ExtractValue(@OrderOrderCheckXML, ‘/ORDERCHECKSS/Idvalue’) ;
SET occurance=length(@IEN)-length(replace(@IEN,’,’,”))+1;
SET my_delimiter=’,’;
IF v_finished = 1 THEN
LEAVE read_loop;
END IF;
IF occurance > 0 then
SET i2=1;
while i2 <= occurance do
— SELECT SPLIT_STRING(@IEN,',',1) ;
set @IEN=right(SPLIT_STRING(@IEN,',',1),length(SPLIT_STRING(@IEN,',',1))+1);
set i2=i2+1;
set @OrderOrderCheckXML=XML;
select @OrderOrderCheckXML;
set @OrdID=id;
Select @OrdID;
set @i=1;
INSERT INTO OrderOrderChecks (`ID`,`OrderCheckID`,`OrderCheck`,`ClinicalDangerLevelCode`,`OrderID`)
values(
SPLIT_STRING(ExtractValue(@OrderOrderCheckXML, '/ORDERCHECKSS/Idvalue[$@i]'),',',1),
ExtractValue(@OrderOrderCheckXML,'/ORDERCHECKSS/ORDERCHECKI[$@i]'),
ExtractValue(@OrderOrderCheckXML,'/ORDERCHECKSS/ORDERCHECKE[$@i]'),
ExtractValue(@OrderOrderCheckXML,'/ORDERCHECKSS/CLINICALDANGERLEVELI[$@i]'),
@OrdID)
ON DUPLICATE KEY UPDATE `OrderCheckID`=ExtractValue(@OrderOrderCheckXML,'/ORDERCHECKSS/ORDERCHECKI[$@i]'),
`OrderCheck`=ExtractValue(@OrderOrderCheckXML,'/ORDERCHECKSS/ORDERCHECKE[$@i]'),
`ClinicalDangerLevelCode`=ExtractValue(@OrderOrderCheckXML,'/ORDERCHECKSS/CLINICALDANGERLEVELI[$@i]');
end while;
END IF;
END LOOP read_loop;
close ID_cursor;
end
any can help me please
One more variant: store procedure that takes table name and field names as arguments. I have replaced LENGTH to CHAR_LENGTH and I have simplified splitted_value expression.
delimiter $$
DROP PROCEDURE IF EXISTS split_value_into_multiple_rows $$
CREATE PROCEDURE split_value_into_multiple_rows(tablename VARCHAR(20), id_column VARCHAR(20), value_column VARCHAR(20), delim CHAR(1))
BEGIN
DECLARE id INT DEFAULT 0;
DECLARE value VARCHAR(255);
DECLARE occurrences INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE splitted_value VARCHAR(20);
DECLARE done INT DEFAULT 0;
DECLARE cur CURSOR FOR SELECT tmp_table1.id, tmp_table1.value FROM tmp_table1 WHERE tmp_table1.value IS NOT NULL AND tmp_table1.value != ”;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET @expr = CONCAT(‘CREATE TEMPORARY TABLE tmp_table1 (id INT NOT NULL, value VARCHAR(255)) ENGINE=Memory SELECT ‘,id_column,’ id, ‘,value_column,’ value FROM ‘,tablename);
PREPARE stmt FROM @expr;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
CREATE TEMPORARY TABLE tmp_table2 (
id INT NOT NULL,
value VARCHAR(255) NOT NULL
) ENGINE=Memory;
OPEN cur;
read_loop: LOOP
FETCH cur INTO id, value;
IF done THEN
LEAVE read_loop;
END IF;
SET occurrences = (SELECT CHAR_LENGTH(value) – CHAR_LENGTH(REPLACE(value, delim, ”)) + 1);
SET i=1;
WHILE i <= occurrences DO
SET splitted_value = (SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(value, delim, i), delim, -1)));
INSERT INTO tmp_table2 VALUES (id, splitted_value);
SET i = i + 1;
END WHILE;
END LOOP;
SELECT * FROM tmp_table2;
DROP TEMPORARY TABLE tmp_table2;
CLOSE cur;
DROP TEMPORARY TABLE tmp_table1;
END; $$
delimiter ;
Example:
CALL split_value_into_multiple_rows('my_contacts', 'contact_id', 'phones', ',');
It should be here in the while loop at the replace stmt also bound:
WHILE i <= occurance DO
SET splitted_value =
(SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(value, bound, i),
LENGTH(SUBSTRING_INDEX(value, bound, i – 1)) + 1), bound, ''));