How to split a resulting column in multiple columns

Question

I have a table like:

id  ip
--  -----------
1   192.168.2.1

And need to get a result like:

id  ip           A    B    C    D
--  -----------  ---  ---  ---  ---
1   192.168.2.1  192  168  2    1

Doable?

possible duplicate of [Break up data into columns in mysql](http://stackoverflow.com/questions/10985085/break-up-data-into-columns-in-mysql) — Kermit, Dec 06 '12 at 19:01
See similar question [here](http://stackoverflow.com/questions/2412895/parse-results-in-mysql-via-regex) — Michael, Dec 06 '12 at 19:08

score 3 · Accepted Answer · answered Dec 06 '12 at 19:00

SELECT `id`, `ip`,
    SUBSTRING_INDEX( `ip` , '.', 1 ) AS a,
    SUBSTRING_INDEX(SUBSTRING_INDEX( `ip` , '.', 2 ),'.',-1) AS b,
    SUBSTRING_INDEX(SUBSTRING_INDEX( `ip` , '.', -2 ),'.',1) AS c,
    SUBSTRING_INDEX( `ip` , '.', -1 ) AS d
FROM unicorns

Source

Example

SET @ip = '192.168.1.1';

SELECT @ip,
    SUBSTRING_INDEX( @ip , '.', 1 ) AS a,
    SUBSTRING_INDEX(SUBSTRING_INDEX( @ip , '.', 2 ),'.',-1) AS b,
    SUBSTRING_INDEX(SUBSTRING_INDEX( @ip , '.', -2 ),'.',1) AS c,
    SUBSTRING_INDEX( @ip , '.', -1 ) AS d

Result

|         @IP |   A |   B | C | D |
-----------------------------------
| 192.168.1.1 | 192 | 168 | 1 | 1 |

How to split a resulting column in multiple columns

1 Answers1

Linked