MYSQL - Split colon separated column values in to new columns

Question

i have a table like this ,

CREATE TABLE IF NOT EXISTS `cms` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cms` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;


INSERT INTO `cms` (`id`, `cms`) VALUES
(1, 'cms:/o:freebsd:freebsd:2.1.7'),
(2, 'cms:/o:netbsd:netbsd:1.0');

from which i need to split cms column values in to four additional columns , hence it has to be separated by colon.

i need output something like this

need query for this

Do you have access to Java? It's super easy to explode a string using Java. After this, bring the data into MySQL. BTW, you're absolutely doing the right thing by normalizing your table data, but MySQL isn't so great in exploding strings. — Tim Biegeleisen, Nov 01 '16 at 06:08
You might find this helpful: http://stackoverflow.com/a/2696901/2022457 — moni_dragu, Nov 01 '16 at 06:20

score 1 · Accepted Answer · answered Nov 01 '16 at 06:45

Try this. It is a little bit dirty, but it works:

select source.*
, left(cms, FirstColon - 1) as CMS2
, mid(cms, FirstColon + 1, SecondColon - FirstColon -1) as Extension 
, mid(cms, SecondColon + 1, ThirdColon - SecondColon -1) as Product
, mid(cms, ThirdColon + 1, FourthColon - ThirdColon -1) as version
from (
    select a.*
    , locate(':', cms) 'FirstColon'
    , locate(':', cms, locate(':', cms) + 1) 'SecondColon'
    , locate(':', cms, locate(':', cms, locate(':', cms) + 1) + 1) 'ThirdColon'
    , locate(':', cms, locate(':', cms, locate(':', cms, locate(':', cms) + 1) + 1) + 1) 'FourthColon'
    from cms a
) source
;

But if you have Microsoft Excel installed, you can try Excel Text To Column tool. Just export your raw data into Microsoft Excel format, and use that tool.

MYSQL - Split colon separated column values in to new columns

1 Answers1