suppose that I have a table with two columns like this:

you see that in second column I have a list of words, seperated by a comma. how can I have a select query that gives a distinct list of words from second column that were seperated by comma like this:

CodePudding user response：

your data

CREATE TABLE mytable(
   ID    INTEGER  NOT NULL 
  ,Teams VARCHAR(36) NOT NULL
);
INSERT INTO mytable
(ID,Teams) VALUES 
(1,'Netherlands,Ecuador,Qatar,Senegal'),
(2,'England,Iran,United States,Team X'),
(3,'Mexico,Argentina,Poland,Saudi Arabia'),
(4,'France,Denmark,Tunisia,Team Y'),
(5,'Spain,Japan,Germany,Team Z'),
(6,'Croatia,Belgium,Canada,Morocco'),
(7,'Brazil,Cameroon,Switzerland,Serbia'),
(8,'Uruguay,Portugal,Ghana,South Korea');

you should create a number table with length of maximum value of comma separated values

CREATE TABLE numbers (
  n INT );

INSERT INTO numbers VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

then join it as follows based on this question

SELECT mytable.id,
       Substring_index(Substring_index(mytable.teams, ',', numbers.n), ',', -1)
       Teams
FROM   numbers
       INNER JOIN mytable
               ON Char_length(mytable.teams) - Char_length(
                                               Replace(mytable.teams, ',',
                                               '')) >=
                  numbers.n - 1
ORDER  BY id,
          n  

fiddle

CodePudding user response：

This is a case of simulating an array in MySQL. Storing data as comma separated strings in the database is highly discouraged, as shree.pat18 suggested. Though some string functions can get your job done, doing this has a downside of making the data handling complex and prone to faults. In this case, since only two columns exist in the table, we may consider dividing each comma-separated string value into individual lesser strings and make each one a different row. (Had we have a more complex table we would have built another table to store the separated string values and Foreign KEY references the original table's id column). Besides, we can add an array index column to mark the position of each value in the array. In all honesty, I do hope you migrate the existing table into a new table . If you do decide to do so, here is a procedure I have written and tested in workbench, supposing the original table is named testtb and the new table is named new_tb.

delimiter //
drop procedure if exists mk_array//
create procedure mk_array()
begin
declare posi int default 1;
declare c_id int;
declare c_teams varchar(200);
declare c_comma_posi int default 1;
declare c_string varchar(30);
declare c_index int default 1;
declare fin bool default false;
declare c cursor for select id, teams from testtb ;
declare continue handler for not found set fin=true;
drop  table if exists new_tb;
CREATE TABLE new_tb (
    id INT,
    team_index INT,
    team VARCHAR(30)
);
open c ;
-- The first loop fetches each row from the original table
row_lp:loop
fetch c into c_id,c_teams;
    if fin=true then
        leave row_lp;
    end if;
-- The second loop separate the comma-separated string into individual lesser strings and insert them into the new table 
        string_lp:loop
        set c_comma_posi=locate(',',c_teams);
            if c_comma_posi =0 then
                set c_string = c_teams;
                set c_comma_posi=1;
                insert new_tb values(c_id,c_index,c_string);
                set c_index=1;
                leave string_lp;
            else 
                set c_string =substring_index(c_teams,',',1) ;
                set c_teams=substring(c_teams,c_comma_posi 1);
                insert new_tb values(c_id,c_index,c_string);
                set c_index=c_index 1;
            end if;
        end loop string_lp;
        
end loop row_lp;

end//

-- call the procedure and check the data in the new table 
call mk_array //
select * from new_tb //