Traverse all strings by delimiter in the field and generate a column with the result

Asked

Viewed 364 times

0

SET @contador := 0;
SELECT SUBSTRING_INDEX(
         SUBSTRING_INDEX("BOX 01;BOX 02;BOX 03;BOX 04;BOX 05;BOX 06;BOX 07;BOX 08;BOX 09;BOX 10;BOX 11;BOX 12;BOX 13;BOX 14;BOX 15;BOX 16;BOX 17;BOX 18;BOX 19;BOX 20;BOX 21;BOX 22;BOX 23;BOX 24;BOX 25;BOX 26;BOX 27;BOX 28;BOX 29;BOX 30"
                         , ';', @contador := @contador + 1)
                       , ';', -1) AS box
       , @contador as contagem FROM galpao

with that select works like a loop for however it only has 13 records and there are 30. in the case in my table the field is type text then independent of how many records exist in the field need that they appear in all in column and not only the 13. I have been researching and it seems that the problem is in the native function SUBSTRING_INDEX q only performs 13 times the search.

detail this has to be on a select - if possible.

mysql sql select

  • I found no problem with your select. when you the select * from galpao has the 30 record ?

  • then, these boxes are all in a field of the branch table of type text and then to separate them I am using delimiters, in case I need to make each box become a row in the result of select ex: box 1 box 2 box 3 box 4 this would be shown here with how many had registered in the field, in which case are 30 more could have much more.

  • CREATE TABLE galpao ( id int(11) NOT NULL AUTO_INCREMENT, nome_galpao varchar(60) NOT NULL, box TEXT NOT NULL, PRIMARY KEY (id) INSERT INTO galpao (id, galpao, box) VALUES(','galpao_1','BOX 01;BOX 02;BOX 03;BOX 04;BOX 05;BOX 06;BOX 07;BOX 08;BOX 09;BOX 10;BOX 11;BOX 12;BOX 13;BOX 14;BOX 15;BOX 16;BOX 17;BOX 18;BOX 19;BOX 20;BOX 21;BOX 22;BOX 23;BOX 24;BOX 26;BOX 27;BOX 28;BOX 29;BOX 30')& #Xa; , accurate of the result in line ex.: box 1 box 2 box 3 box 4&#Xa.

No answers

Browser other questions tagged mysql sql select

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