SUBSTRING_INDEX in mysql

Navigation
0

I have these values in the database table, where in the field the comma gathers separates as if it were a column. The data looks like this: 

Id------arrachar
1       2018-04-26,Peq_Almoço,14,Almoço,12,Almoço_(Dieta),2,Lanche,14,Jantar,10,Jantar_(Dieta),10
2       2018-04-27,Peq_Almoço,15,Almoço,12,Almoço_(Dieta),3,Lanche,15,Jantar,12,Jantar_(Dieta),2

Now I want to separate each value between commas with SUBSTRING_INDEX . I have this code, where only the date is correct: 

SELECT SUBSTRING_INDEX(arrachar, ',', 1) AS 'data', 
       SUBSTRING_INDEX(arrachar, ',', 2) AS 'Pequeno Almoço',
       SUBSTRING_INDEX(arrachar, ',', 3) AS 'Quantidade Peq. Alm.',
       SUBSTRING_INDEX(arrachar, ',', 4) AS 'Almoço',
       SUBSTRING_INDEX(arrachar, ',', 5) AS 'Quantidade Almoço',
       SUBSTRING_INDEX(arrachar, ',', 6) AS 'Lanche',
       SUBSTRING_INDEX(arrachar, ',', 7) AS 'Quantidade Lanche',
       SUBSTRING_INDEX(arrachar, ',', 8) AS 'Jantar',
       SUBSTRING_INDEX(arrachar, ',', 9) AS 'Quantidade Jantar',
       SUBSTRING_INDEX(arrachar, ',', 10) AS 'Jantar Dieta',
       SUBSTRING_INDEX(arrachar, ',', 11) AS 'Quantidade Jan. Die.'

FROM centrodb.marcacaoInfancia

Result: 

data         Pequeno Almoço          Quantidade Peq. Alm.              Almoço                        ...   ...   ...
2018-04-26  2018-04-26,Peq_Almoço   2018-04-26,Peq_Almoço,14    2018-04-26,Peq_Almoço,14,Almoço
2018-04-27  2018-04-27,Peq_Almoço   2018-04-27,Peq_Almoço,15    2018-04-27,Peq_Almoço,15,Almoço

I will show the problem, in Pequeno Almoço should only have Peq_Almoço and not 2018-04-26,Peq_Almoço .

    
asked by anonymous 24.04.2018 / 13:59

2 answers

1

Try using it like this: 

SELECT SUBSTRING_INDEX(arrachar, ',', 1) AS 'data', 
       SUBSTRING_INDEX(SUBSTRING_INDEX(arrachar, ',', 2), '.', -1) AS 'Pequeno Almoço',
       SUBSTRING_INDEX(SUBSTRING_INDEX(arrachar, ',', 3), '.', -1) AS 'Quantidade Peq. Alm.',
       SUBSTRING_INDEX(SUBSTRING_INDEX(arrachar, ',', 4), '.', -1) AS 'Almoço',
       ...
    
24.04.2018 / 14:13
0

I do not guarantee it's the best alternative, but it's still one: 

SELECT SUBSTRING_INDEX(arrachar, ",", 1) AS 'data',
SUBSTR(arrachar, LENGTH(SUBSTRING_INDEX(arrachar, ",", 1)) + 2, LENGTH(SUBSTRING_INDEX(arrachar, ",", 2)) - (LENGTH(SUBSTRING_INDEX(arrachar, ",", 1)) + 2)) AS 'Pequeno Almoço',
SUBSTR(arrachar, LENGTH(SUBSTRING_INDEX(arrachar, ",", 2)) + 2, LENGTH(SUBSTRING_INDEX(arrachar, ",", 3)) - (LENGTH(SUBSTRING_INDEX(arrachar, ",", 2)) + 2)) AS 'Quantidade Peq. Alm.',
SUBSTR(arrachar, LENGTH(SUBSTRING_INDEX(arrachar, ",", 3)) + 2, LENGTH(SUBSTRING_INDEX(arrachar, ",", 4)) - (LENGTH(SUBSTRING_INDEX(arrachar, ",", 3)) + 2)) AS 'Almoço',
SUBSTR(arrachar, LENGTH(SUBSTRING_INDEX(arrachar, ",", 4)) + 2, LENGTH(SUBSTRING_INDEX(arrachar, ",", 5)) - (LENGTH(SUBSTRING_INDEX(arrachar, ",", 4)) + 2)) AS 'Quantidade Almoço',
SUBSTR(arrachar, LENGTH(SUBSTRING_INDEX(arrachar, ",", 5)) + 2, LENGTH(SUBSTRING_INDEX(arrachar, ",", 6)) - (LENGTH(SUBSTRING_INDEX(arrachar, ",", 5)) + 2)) AS 'Lanche',
SUBSTR(arrachar, LENGTH(SUBSTRING_INDEX(arrachar, ",", 6)) + 2, LENGTH(SUBSTRING_INDEX(arrachar, ",", 7)) - (LENGTH(SUBSTRING_INDEX(arrachar, ",", 6)) + 2)) AS 'Quantidade Lanche',
SUBSTR(arrachar, LENGTH(SUBSTRING_INDEX(arrachar, ",", 7)) + 2, LENGTH(SUBSTRING_INDEX(arrachar, ",", 8)) - (LENGTH(SUBSTRING_INDEX(arrachar, ",", 7)) + 2)) AS 'Jantar',
SUBSTR(arrachar, LENGTH(SUBSTRING_INDEX(arrachar, ",", 8)) + 2, LENGTH(SUBSTRING_INDEX(arrachar, ",", 9)) - (LENGTH(SUBSTRING_INDEX(arrachar, ",", 8)) + 2)) AS 'Quantidade Jantar',
SUBSTR(arrachar, LENGTH(SUBSTRING_INDEX(arrachar, ",", 9)) + 2, LENGTH(SUBSTRING_INDEX(arrachar, ",", 10)) - (LENGTH(SUBSTRING_INDEX(arrachar, ",", 9)) + 2)) AS 'Jantar Dieta',
SUBSTR(arrachar, LENGTH(SUBSTRING_INDEX(arrachar, ",", 10)) + 2, LENGTH(SUBSTRING_INDEX(arrachar, ",", 11)) - (LENGTH(SUBSTRING_INDEX(arrachar, ",", 10)) + 2)) AS 'Quantidade Jan. Die.';
    
24.04.2018 / 14:25
Show alert if no records found - Ireport MySQL Workbench Access Denied