Complex split comma value in MySQL

Question

below is my problem

I have the string "1,2,3,4,5". I want to split the values and put into one temp table BUT i want to intercalate by 1 one position, i mean the output will be

N°1 | N°2
---------
1   |  2
2   |  3
3   |  4
4   |  5

I hope someone solve my problem. Thanks!

Are you trying to do this in a single MySQL query, or are you using some programming language to interface with MySQL? If you are using a programming language, perhaps you could specify which one and what you've tried so far. — Jeff Richards, Nov 23 '17 at 19:47
I need to do only in MySQL, i can do it in PHP but that is not a option — fercaveri, Nov 23 '17 at 19:56
SO is not a free coding service. Post your query. If it doesn't work, we can help. — Eric, Nov 23 '17 at 20:24

score 3 · Accepted Answer · 2017-11-23T22:41:25.200

3

For a simple table like the one created by the below syntax

CREATE TABLE `demo2` (
  `no1` int(11) NOT NULL,
  `no2` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

You could try a stored procedure such as the one below.

Example of adding +1 to each value in string on each insert

CREATE PROCEDURE `dowhile`(IN `s1` VARCHAR(255))
BEGIN
SET @length = CHAR_LENGTH(`s1`);
SET @counter = 1;
WHILE @counter <= @length DO
    SET @char = SUBSTRING(`s1`, @counter, 1);
    IF STRCMP(@char, ",") <> 0 THEN
    INSERT INTO `demo2` (`no1`,`no2`) VALUES (@char,@char+1);
    END IF;
    SET @counter = @counter + 1;
  END WHILE;
  END

Example of adding in field no1 the nth element and in no2 the nth+1 arithmetic element in string (not counting comma)

CREATE PROCEDURE `dowhile`(IN `s1` VARCHAR(255))
BEGIN
    SET @length = CHAR_LENGTH(`s1`);
    SET @counter = 1;
    WHILE @counter <= @length DO
        SET @next_el_counter = @counter + 2;
        SET @char = SUBSTRING(`s1`, @counter, 1);
        IF STRCMP(@char, ",") <> 0 THEN
        INSERT INTO `demo2` (`no1`,`no2`) VALUES (@char,SUBSTRING(`s1`, @next_el_counter, 1));
        END IF;
        SET @counter = @counter + 1;
      END WHILE;
      END

Don't forget to call it...

CALL `dowhile`('1,2,3,4,5');

edited Nov 23 '17 at 22:41

answered Nov 23 '17 at 21:18

Liked the approach(already +1'd it). One question though: Does `INSERT INTO `demo2` (`no1`,`no2`) VALUES (@char,@char+1);` only work when the strings are increment with 1 or am I missing something here? – Harshil Doshi Nov 23 '17 at 21:31
@Harshil if your input string is `a,b,c,d,e` then it will add in `no1` `a,b,c,d,e` and in `no2` `a+1,b+1,c+1,d+1,e+1`. – Nov 23 '17 at 21:45
Yes that's what I understood. It won't work if string will be like : `e,d,c,b,a` – Harshil Doshi Nov 23 '17 at 21:52
@Harshil for a string like `e,d,c,b,a` you would expect as output `e+1,d+1,c+1,b+1,a+1`. It does not sort as it is now. In that case it would need some modifications. – Nov 23 '17 at 22:02
@Harshil this only works for this particular arithmetic strings such as `1,2,3,4,5` or `2,3,4,5,6...` or any similar sorted incremented by 1. In any other case this does not work and needs modification. I mean it does not put in field `no2` the second element of the input string in the first insert of the loop. In that case i will add a small edit. – Nov 23 '17 at 22:19

Harshil Doshi · Answer 2 · 2017-11-23T20:21:53.440

First of all, I will request you to go through this LINK link in order to realize the cons of storing a delimited list in a column.

Anyway, if you achieve this task using MySql, then you need to do it using Procedure and Function as follows:

Step 1: Create a Function to split data with delimiter , with given position.

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, '');

Now, the above function will split data only for the given position. In order to split whole string, we need to iterate this function. That's our step 2.

Step 2: Create Procedure to iterate the above function;

CREATE PROCEDURE ABC(IN fullstr varchar(1000))
   BEGIN
      DECLARE a INT Default 0;
      DECLARE str VARCHAR(255);
      DECLARE prev VARCHAR(255);
      simple_loop: LOOP
         SET a=a+1;
         IF a=1 THEN
             SET prev=SPLIT_STR(fullstr,",",a);
             SET a=a+1;
         END IF;
         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 t1 values (prev,str);
         SET prev=str;
   END LOOP simple_loop;

Step 3: Call Procedure:

       call abc(columnName);

Step 4: Get data from temp table:

select *
from temp;

Hope it helps!

Complex split comma value in MySQL

2 Answers2