Mysql string split [duplicate]

Question

Possible Duplicate:
Can Mysql Split a column ?

I have one string column and one comma separated column as below

Example, I have the table,

Col1 | col2
1    | a,b,c
2    | d,e

From above, I want

Col1,Col2
1   ,a
1   ,b
1   ,c
2   ,d
2   ,e

How to achieve this

It is not the Exact Duplicate.

Sorry, but I smell bad database design here. You should use a table that looks exactly like your second example (without the commas). — kapa
– kapa, Commented May 27, 2011 at 12:22
This column has been asked multiple times on stack overflow. See stackoverflow.com/questions/1096679/can-mysql-split-a-column — garnertb
– garnertb, Commented May 27, 2011 at 12:24
@hsz : No need of that commas @ user589983: Could you please provide me the solution in simple way — StackflowLover
– StackflowLover, Commented May 27, 2011 at 12:56

Johan · Accepted Answer · 2011-05-27 14:00:10Z

23

You can do this using a stored procedure

DELIMITER $$

CREATE FUNCTION strSplit(x VARCHAR(65000), delim VARCHAR(12), pos INTEGER) 
RETURNS VARCHAR(65000)
BEGIN
  DECLARE output VARCHAR(65000);
  SET output = REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos)
                 , LENGTH(SUBSTRING_INDEX(x, delim, pos - 1)) + 1)
                 , delim
                 , '');
  IF output = '' THEN SET output = null; END IF;
  RETURN output;
END $$


CREATE PROCEDURE BadTableToGoodTable()
BEGIN
  DECLARE i INTEGER;

  SET i = 1;
  REPEAT
    INSERT INTO GoodTable (col1, col2)
      SELECT col1, strSplit(col2, ',', i) FROM BadTable
      WHERE strSplit(col2, ',', i) IS NOT NULL;
    SET i = i + 1;
    UNTIL ROW_COUNT() = 0
  END REPEAT;
END $$

DELIMITER ;

answered May 27, 2011 at 14:00

Johan

77.4k28 gold badges204 silver badges346 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql string split [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related