Can you parse a comma separated string into a temp table in MySQL using RegEx?
'1|2|5|6' into temp table with 4 rows.
Can you parse a comma separated string into a temp table in MySQL using RegEx?
'1|2|5|6' into temp table with 4 rows.
This is pretty much the same question as Can Mysql Split a column?
MySQL doesn't have a split string function so you have to do work arounds. You can do anything with the data once you split it using one of the methods listed on the answer page above.
You can loop over that custom function and break when it returns empty, you'll have to play and learn some syntax (or at least I would) but the syntax for a FOR loop in mysql is here: http://www.roseindia.net/sql/mysql-example/for.shtml
You can iterate over it, incrementing the position in the function below:
CREATE FUNCTION SPLIT_STR(
x VARCHAR(255),
delim VARCHAR(12),
pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
delim, '');
(Credit: https://blog.fedecarg.com/2009/02/22/mysql-split-string-function/ )
Which should return '' if no match is found, so break the loop if no match is found. This will allow you to with only mysql parse over the split string and run the insert queries into a temp table. But man why not just use a scripting language like php for that kind of work? :(
Code for loop syntax:
DELIMITER $$
CREATE PROCEDURE ABC(fullstr)
BEGIN
DECLARE a INT Default 0 ;
DECLARE str VARCHAR(255);
simple_loop: LOOP
SET a=a+1;
SET str=SPLIT_STR(fullstr,"|",a);
IF str='' THEN
LEAVE simple_loop;
END IF;
#Do Inserts into temp table here with str going into the row
insert into my_temp_table values (str);
END LOOP simple_loop;
END $$
I have done this, for when you don't have table values and so on:
select *
from(
select c, SUBSTRING_INDEX(SUBSTRING_INDEX('1|2|5|6', '|', c+1), '|', -1) as name
from(
SELECT (TWO_1.SeqValue + TWO_2.SeqValue + TWO_4.SeqValue + TWO_8.SeqValue + TWO_16.SeqValue + TWO_32.SeqValue) c
FROM (
SELECT 0 SeqValue UNION ALL SELECT 1 SeqValue) TWO_1
CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 2 SeqValue) TWO_2
CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 4 SeqValue) TWO_4
CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 8 SeqValue) TWO_8
CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 16 SeqValue) TWO_16
CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 32 SeqValue) TWO_32
) as b
WHERE c <= (CHAR_LENGTH('1|2|5|6') - CHAR_LENGTH(REPLACE('1|2|5|6', '|', '')))
) as a;
May not be the best answer, but works without aid of functions and procedures, no additional tables etc.
You can use regular expression in MySQL to specify a pattern for a complex search, you cannot parse the strings.
But you can build INSERT query with the help of REPLACE and CONCATENATE to save data to temp table.
DELIMITER $$
CREATE PROCEDURE SPLIT_VALUE_STRING()
BEGIN
SET @String = '1,22,333,444,5555,66666,777777';
SET @Occurrences = LENGTH(@String) - LENGTH(REPLACE(@String, ',', ''));
myloop: WHILE (@Occurrences > 0)
DO
SET @myValue = SUBSTRING_INDEX(@String, ',', 1);
IF (@myValue != '') THEN
/* my code... */
ELSE
LEAVE myloop;
END IF;
SET @Occurrences = LENGTH(@String) - LENGTH(REPLACE(@String, ',', ''));
IF (@occurrences = 0) THEN
LEAVE myloop;
END IF;
SET @String = SUBSTRING(@String,LENGTH(SUBSTRING_INDEX(@String, ',', 1))+2);
END WHILE;
END $$
select distinct
SUBSTRING_INDEX(SUBSTRING_INDEX('1,2,3,4', ',', numbers.n), ',', -1) name
from
(select @rownum := @rownum + 1 as n
from YourTable
cross join (select @rownum := 0) r
) numbers
order by
n
I found good solution for this
https://forums.mysql.com/read.php?10,635524,635529
Thanks to Peter Brawley
Trick: massage a Group_Concat() result on the csv string into an Insert...Values... string:
drop table if exists t;
create table t( txt text );
insert into t values('1,2,3,4,5,6,7,8,9');
drop temporary table if exists temp;
create temporary table temp( val char(255) );
set @sql = concat("insert into temp (val) values ('", replace(( select group_concat(distinct txt) as data from t), ",", "'),('"),"');");
prepare stmt1 from @sql;
execute stmt1;
select distinct(val) from temp;
+------+
| val |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+------+
Also if you just want to join some table to list of id you can use LIKE operator. There is my solution where I get list of id from blog post urls, convert them to comma separated list started and finished with commas and then join related products by id list with LIKE operator.
SELECT b2.id blog_id, b2.id_list, p.id
FROM (
SELECT b.id,b.text,
CONCAT(
",",
REPLACE(
EXTRACTVALUE(b.text,'//a/@id')
, " ", ","
)
,","
) AS id_list
FROM blog b
) b2
LEFT JOIN production p ON b2.id_list LIKE CONCAT('%,',p.id,',%')
HAVING b2.id_list != ''
If the text you are trying to split contains mutli-byte characters, this approach will break down because of LENGTH being calculated incorrectly. For such cases, the following version with CHAR_LENGTH instead of LENGTH works:
CREATE DEFINER=`root`@`localhost` FUNCTION `strSplit`(
`src` MEDIUMTEXT CHARACTER SET utf8,
`delim` VARCHAR(12),
`pos` INTEGER
)
RETURNS mediumtext
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE output MEDIUMTEXT CHARACTER SET utf8;
SET output = REPLACE(SUBSTRING(SUBSTRING_INDEX(src, delim, pos) ,
CHAR_LENGTH(SUBSTRING_INDEX(src, delim, pos - 1)) + 1) , delim , '');
IF output = '' THEN SET output = null; END IF;
RETURN output;
END
Reference: http://www.shakedos.com/2011/Nov/23/mysql-split-string-function-fix-split_str.html
Just because I really love resurrecting old questions:
CREATE PROCEDURE `SPLIT_LIST_STR`(IN `INISTR` TEXT CHARSET utf8mb4, IN `ENDSTR` TEXT CHARSET utf8mb4, IN `INPUTSTR` TEXT CHARSET utf8mb4, IN `SEPARATR` TEXT CHARSET utf8mb4)
BEGIN
SET @I = 1;
SET @SEP = SEPARATR;
SET @INI = INISTR;
SET @END = ENDSTR;
SET @VARSTR = REPLACE(REPLACE(INPUTSTR, @INI, ''), @END, '');
SET @N = FORMAT((LENGTH(@VARSTR)-LENGTH(REPLACE(@VARSTR, @SEP, '')))/LENGTH(@SEP), 0)+1;
CREATE TEMPORARY TABLE IF NOT EXISTS temp_table(P1 TEXT NULL);
label1: LOOP
SET @TEMP = SUBSTRING_INDEX(@VARSTR, @SEP, 1);
insert into temp_table (`P1`) SELECT @TEMP;
SET @I = @I + 1;
SET @VARSTR = REPLACE(@VARSTR, CONCAT(@TEMP, @SEP), '');
IF @N >= @I THEN
ITERATE label1;
END IF;
LEAVE label1;
END LOOP label1;
SELECT * FROM temp_table;
END
Which Produces:
P1
1
2
3
4
When Using CALL SPLIT_LIST_STR('("', '")', '("1", "2", "3", "4")', '", "');
I might pop up later to tide up the code a bit more! Cheers!