[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.....