Mangiare Senza Glutine disponibile su App Store

Per altre informazioni scrivi a fabriziocaldarelli@negusweb.it

MySQL split string per campi da usare come list

Da Programmazione Software.

Due tabelle, una con un campo che contiene la lista di id di riferimento dell'altra.

Esempio:

Studenti: id, nome, cognome
Corsi: id, lista_id_studenti, nome_corso

Il campo "lista_id_studenti" della tabella corsi è un campo di testo con gli id separati da ",".


Se vogliamo avere la lista degli studenti che hanno partecipato al corso con id = 4, per esempio, scriveremmo:

SELECT * FROM studenti WHERE id IN (SELECT lista_id_studenti FROM corsi WHERE id_corso = 4)

Ora, poichè il valore ritornato dalla subquery è un testo, verrà utilizzato come risultato della query solo il primo id, perdendo tutti gli altri.

A questo punto c'è bisogno di una query che splitti questo testo in tante variabili di testo circoscritte da apici e separate l'una dall'altra da virgola.

Esempio:

Mettiamo che gli id degli studenti che hanno partecipato al corso con id_corso = 4 siano id =1, id = 2, id = 3, quindi il campo lista_id_studenti della tabella corsi per il corso con id_corso = 4 sarà "1,2,3".

Il risultato della query

SELECT lista_id_studenti FROM corsi WHERE id_corso = 4

sarà "1,2,3".

Se passiamo questo valore alla IN della query principale, otterremo come risultato solo lo studente con id = 1.

Per ottenere tutti gli studenti di quella lista dovremo splittare la string in tante sottostringhe separate da virgola quanti sono i valori, ovvero: "1","2","3".

Per fare ciò possiamo usare questa procedura:

DELIMITER //
CREATE PROCEDURE split_value(IN INPUT TEXT, IN delimiter VARCHAR(10))
BEGIN
    DECLARE cur_position INT DEFAULT 1 ;
    DECLARE remainder TEXT;
    DECLARE cur_string VARCHAR(1000);
    DECLARE delimiter_length TINYINT UNSIGNED;
 
    DROP TEMPORARY TABLE IF EXISTS SplitValues;
    CREATE TEMPORARY TABLE SplitValues (
        VALUE VARCHAR(1000) NOT NULL PRIMARY KEY
    ) ENGINE=MEMORY;
 
    SET remainder = INPUT;
    SET delimiter_length = CHAR_LENGTH(delimiter);
 
    WHILE CHAR_LENGTH(remainder) > 0 AND cur_position > 0 DO
        SET cur_position = INSTR(remainder, delimiter);
        IF cur_position = 0 THEN
            SET cur_string = remainder;
        ELSE
            SET cur_string = LEFT(remainder, cur_position - 1);
        END IF;
        IF TRIM(cur_string) != '' THEN
            INSERT INTO SplitValues VALUES (cur_string);
        END IF;
        SET remainder = SUBSTRING(remainder, cur_position + delimiter_length);
    END WHILE;
 
END //
DELIMITER ;

che crea una tabella temporanea, di nome "SplitValues" con un unico campo, "value", che contiene il singolo valore. A questo punto basta fare una select da questa tabella ed ottenere i singoli valori.

Quindi in definitiva le operazioni da fare sono:

DECLARE strId TEXT;
CALL split_values(SELECT lista_id_studenti FROM corsi WHERE id_corso = 4,",");
SELECT * FROM studenti WHERE id IN (SELECT VALUE FROM SplitValues);


Allegati