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
– Tim Biegeleisen, Commented Nov 1, 2016 at 6:08
You might find this helpful: stackoverflow.com/a/2696901/2022457 — moni_dragu
– moni_dragu, Commented Nov 1, 2016 at 6:20

Hermanto · Accepted Answer · 2016-11-01 06:45:25Z

1

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.

answered Nov 1, 2016 at 6:45

Hermanto

5526 silver badges16 bronze badges

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

Collectives™ on Stack Overflow

MYSQL - Split colon separated column values in to new columns

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related