SUBSTRING_INDEX in mysql

Asked

Viewed 402 times

0

I have these values in the table of the database, where in the field array the comma separates as if it were a column. The data is this way:

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 the 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

Upshot:

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’ll show you the problem, no Pequeno Almoço should only have Peq_Almoço and not 2018-04-26,Peq_Almoço.

mysql

2 answers

1


Try to use 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`,
       ...

  • It doesn’t work.... I put it like this SUBSTRING_INDEX((arrachar, ',', 2), '.', -1) AS ``Pequeno Almoço,mas dá este erroOperand should contain 1 column(s)`

  • There is a small syntax error here: SUBSTRING_INDEX((arrachar, ',', 2), '.', -1). Utilize SUBSTRING_INDEX(SUBSTRING_INDEX(arrachar, ',', 2), '.', -1).

  • Still have the same error? If yes, check the function call SUBSTRING_INDEX. She waits for the parameters spine, character to be sought and occurrence counter. SUBSTRING_INDEX(*(arrachar, ',', 2)*, ',', -1). The section between asterisks is where your problem is.

  • It already worked, there’s only one little mistake in your syntax instead SUBSTRING_INDEX(SUBSTRING_INDEX(arrachar, ',', 2), '.', -1) the correct one is this SUBSTRING_INDEX(SUBSTRING_INDEX(arrachar, ',', 2), ',', -1), only changes this part , ',', -1), change the point by the comma

0

I can’t guarantee it’s the best alternative, but it’s still a:

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.`;

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.