Separating query values on MySQL

Question

Is there anyway to separate values within a single record and create a new record with this separate information. Is this possible with a query? For example, a value in a table is as following:

        Single COL
level1Data.level2Data.level3Data 
level1Data.level2Data.level3Data 
level1Data.level2Data.level3Data

converted to:

 level1Col   |   level2Col   |  level3Col
             |               |
Level1Data   |  Level2Data   |  Level3Data
Level1Data   |  Level2Data   |  Level3Data
Level1Data   |  Level2Data   |  Level3Data

Have you tried this? http://stackoverflow.com/questions/471914/can-you-split-explode-a-field-in-a-mysql-query — rlanvin, Aug 26 '15 at 12:33

score 3 · Accepted Answer · answered Aug 26 '15 at 12:40

3

use SUBSTRING_INDEX

SELECT SUBSTRING_INDEX(val, '.', 1) AS c1,
       SUBSTRING_INDEX(SUBSTRING_INDEX(val, '.', 2), '.', -1) AS c2,
       SUBSTRING_INDEX(SUBSTRING_INDEX(val, '.', -1), '.', 1) AS c3 
FROM table

answered Aug 26 '15 at 12:40

mynawaz

1,599
1
9
16

score 1 · Answer 2 · answered Aug 26 '15 at 12:39

I think that the only option here is to substring the field string in a similar way :

SUBSTRING(SingleCOL, 0, LOCATE('.', SingleCOL)) AS level1Col,
SUBSTRING(SingleCOL, LOCATE('.', SingleCOL), LOCATE('.', SingleCOL, LOCATE('.', SingleCOL))) AS level2Col

score 1 · Answer 3 · answered Aug 26 '15 at 12:44

1

try it-

SELECT 'level1Data.level2Data.level3Data', 
SUBSTRING_INDEX('level1Data.level2Data.level3Data','.',1), SUBSTRING_INDEX(SUBSTRING_INDEX('level1Data.level2Data.level3Data','.',2),'.',-1),
SUBSTRING_INDEX('level1Data.level2Data.level3Data','.',-1)

put your column name where string is updated.

answered Aug 26 '15 at 12:44

Zafar Malik

6,734
2
19
30

Thanks for the response, I tried this and it worked also. – Etcetera Aug 26 '15 at 14:41

Separating query values on MySQL

3 Answers3