[SQLite Tutorial] Wie bekommt man in SQLite einen SplitString hin?

[Zvoni]

Mahlzeit,

in einem anderen Forum hatte ein User die Frage gestellt:
"Ich habe eine SQLite-Datenbank, und in einer Tabelle habe ich ein Feld, in welchem pro Zeile ein durch Trennzeichen getrennter Text steht.
Ich brauche diesen Text aus dem Feld jedoch aufgetrennt"

Ergo: Im Prinzip das Gegenteil von group_concat
Nebenbemerkung: Ich glaube mich daran zu erinnern, dass MS SQL-Server hierfür CROSS APPLY bereitstellt, bin mir aber nicht sicher.
Unabhängig davon, konnte ich in die Richtung nicht wirklich was für SQLite finden (Nur Beispiele, welche mir jedoch die Inspiration bzw. den Schubs in die richtige Richtung gegeben haben), also habe ich mich hingesetzt.

Nebenbemerkung 2: Es ist natürlich klar, dass das extrem nach "falschem Design" stinkt. Bitte also keine Diskussion darüber, dass man das Feld rauswirft, und daraus eine Detail-Tabelle mit Fremdschlüssel macht.

Es geht bei meiner Lösung ausschliesslich darum, die originale Forderung umzusetzen.

Setup (Ich benutze die gleichen Tabellen/Spaltennamen wie bei dem User):

CREATE TABLE "cards" (
    "ID"    INTEGER,
    "Keywords"    TEXT,
    PRIMARY KEY("ID")
)

Beispieldaten

INSERT INTO "main"."cards" ("ID", "Keywords") VALUES ('1', 'Text1;text2;text3;text4');
INSERT INTO "main"."cards" ("ID", "Keywords") VALUES ('2', 'Othertext1;Othertext2;OtherText3');

Query

WITH
    DT(Delimiter) AS (VALUES(';')), -- Hier ggfs. das Trennzeichen ändern
    split(id,last,rest) AS
        (SELECT ID, '', Keywords FROM Cards
         UNION ALL
         SELECT ID,
         IIF(instr(rest,Delimiter)>0,substr(rest,1,instr(rest,Delimiter)-1),substr(rest,1)),
         IIF(instr(rest,Delimiter)>0,substr(rest,instr(rest,Delimiter)+1) ,'')
         FROM split INNER JOIN DT ON 1=1 WHERE rest<>'')
     
SELECT ID, last As SplitText FROM split WHERE last<>''
ORDER BY ID, lower(SplitText)

Resultat:

ID	Keywords
1	Text1
1	text2
1	text3
1	text4
2	Othertext1
2	Othertext2
2	OtherText3

Hinweise: Funktioniert natürlich auch nur für gezeigtes Beispiel. "Eingebettete" Trennzeichen werden gnadenlos gefunden.
Andererseits werden aufeinanderfolgende Trennzeichen ignoriert!

Viel Spass

 

[Zvoni]

Nachtrag:
Ich bin mir sicher (kann aber nicht testen, irgendwelche online-fiddles mal aussen vor), dass das auch für andere DBMS so oder ähnlich funktioniert, solange sie rekursive CTE's verstehen.
Oben gezeigtes CTE "split" ist rekursiv!!
SQLite benötigt kein Keyword "RECURSIVE" wie andere DBMS.

Kann auch keine Aussage über Performance machen.
Keine Ahnung, ob bei ein paar Millionen Zeilen der Rechner dann nen Tritt in die Klöten bekommt.....

 

[Spyke]

Mich hats jetzt mal interessiert wie man unter postgre einfach splitten könnte
(folgendes laut Postgre 9.6)

SELECT string_to_array('Text1;text2;text3;text4', ';') -- gibt Array zurück

SELECT unnest(string_to_array('Text1;text2;text3;text4', ';')) --  zuerst als Array und mit unnest als Rows

SELECT regexp_split_to_array('Text1;text2;text3;text4', ';') -- gibt Array zurück

SELECT regexp_split_to_table('Text1;text2;text3;text4', ';') -- gibt Rows zurück

SELECT split_part('Text1;text2;text3;text4', ';', 2) -- liefert direkt einen Wert laut Index zurück

SELECT wertNew FROM
(
    SELECT id, unnest(string_to_array(wert, ';')) AS wertNew
    FROM
    (
        -- inner SELECT weil wollt jetzt keine Tabelle anlegen
        SELECT 1 AS id, 'Text1;text2;text3;text4' AS wert 
        UNION ALL
        SELECT 2 AS id, 'Othertext1;Othertext2;OtherText3' AS wert
    ) AS innerX
) AS x
ORDER BY id, lower(wertNew)

 

[Zvoni]

Mich hats jetzt mal interessiert wie man unter postgre einfach splitten könnte
(folgendes laut Postgre 9.6)

SELECT string_to_array('Text1;text2;text3;text4', ';') -- gibt Array zurück

SELECT unnest(string_to_array('Text1;text2;text3;text4', ';')) --  zuerst als Array und mit unnest als Rows

SELECT regexp_split_to_array('Text1;text2;text3;text4', ';') -- gibt Array zurück

SELECT regexp_split_to_table('Text1;text2;text3;text4', ';') -- gibt Rows zurück

SELECT split_part('Text1;text2;text3;text4', ';', 2) -- liefert direkt einen Wert laut Index zurück

SELECT wertNew FROM
(
    SELECT id, unnest(string_to_array(wert, ';')) AS wertNew
    FROM
    (
        -- inner SELECT weil wollt jetzt keine Tabelle anlegen
        SELECT 1 AS id, 'Text1;text2;text3;text4' AS wert
        UNION ALL
        SELECT 2 AS id, 'Othertext1;Othertext2;OtherText3' AS wert
    ) AS innerX
) AS x
ORDER BY id, lower(wertNew)

Nice.
Die für mich interessante Frage ist in dem Zusammenhang: Bekommst du die ID mitgeliefert?
Ich denke ja, wenn ich dein SQL sehe.

Unabhängig davon, dass sowas ein "Mis-Design" ist, ist es immer ein Thema wie man die ID zu JEDEM Begriff zurückerhält (wie eben bei einer Detail-Tabelle über den Foreign Key)