String in MySQL aufteilen

Mit SUBSTRING_INDEX() können Sie in MySQL Text vor oder nach einem Trennzeichen auslesen. Soll jeder Teil als eigene Zeile erscheinen, kombinieren Sie die Funktion mit einer Zahlenfolge. Sind die Werte bereits als JSON-Array gespeichert, können Sie dessen Elemente mit JSON-Funktionen auslesen; beliebige Texte mit Trennzeichen werden dadurch nicht aufgeteilt.

Mit SUBSTRING_INDEX() Text rund um ein Trennzeichen auslesen

SUBSTRING_INDEX(str, delim, count) gibt bei einem positiven count den Teil von str vor dem count-ten Vorkommen von delim zurück. Bei einem negativen count gibt die Funktion den Teil rechts vom entsprechenden Vorkommen zurück. Das Trennzeichen ist eine Zeichenfolge und kann aus mehreren Zeichen bestehen. Ist das angeforderte Vorkommen nicht vorhanden, gibt die Funktion die gesamte Eingabe zurück.

Bei dem durch Kommas getrennten Wert apple,banana,cherry gibt beispielsweise SUBSTRING_INDEX(value, ',', 1) den Wert apple zurück, während SUBSTRING_INDEX(value, ',', -1) cherry zurückgibt. Mit einem verschachtelten Aufruf lässt sich ein mittleres Feld isolieren: SUBSTRING_INDEX(SUBSTRING_INDEX(value, ',', 2), ',', -1) gibt banana zurück. Ersetzen Sie value durch einen Spaltennamen, um den Ausdruck auf Tabellendaten anzuwenden.

Steht zwischen zwei Adressteilen ein Zeilenumbruch, verwenden Sie SUBSTRING_INDEX(address, '\n', 1) für den Text vor dem ersten Zeilenumbruch und SUBSTRING_INDEX(address, '\n', -1) für den Text nach dem letzten Zeilenumbruch. In einem MySQL-Stringliteral steht \n bei der üblichen Behandlung von Escape-Sequenzen für einen Zeilenumbruch. In SQL-Modi, die Backslash-Escapes deaktivieren, müssen Sie das Trennzeichen entsprechend angeben.

Die SQL-Ausdrücke werden hier inline gezeigt, da die konfigurierte Laufzeitumgebung des Artikels kein MySQL-SQL ausführt. Führen Sie sie in Ihrer MySQL-Datenbank aus, nachdem Sie den Beispielwert oder die Beispielspalte durch Ihre eigenen Daten ersetzt haben.

Eine Zeichenfolge mit Trennzeichen in Zeilen aufteilen

Um einen Wert pro Zeile zurückzugeben, wenden Sie den verschachtelten Ausdruck mit SUBSTRING_INDEX() auf eine Folge von Ganzzahlen an. Die folgende veranschaulichende Abfrage verwendet eine abgeleitete Zahlenfolge von 1 bis 5:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('apple,banana,cherry', ',', numbers.n), ',', -1) AS fruit FROM (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) AS numbers WHERE numbers.n <= LENGTH('apple,banana,cherry') - LENGTH(REPLACE('apple,banana,cherry', ',', '')) + 1;

Für diese Eingabe wählt der Ausdruck das erste, zweite und dritte Feld aus. Die WHERE-Bedingung zählt die Kommas und addiert eins, um die Anzahl der zurückzugebenden Felder zu bestimmen. Eine Ergebnistabelle würde apple, banana und cherry enthalten, jeweils einen Wert pro Zeile. Diese Abfrage ist ein MySQL-Beispiel und wird von der konfigurierten Laufzeitumgebung nicht ausgeführt.

Die Zahlenfolge ist begrenzt: Diese abgeleitete Tabelle kann höchstens fünf Zeilen erzeugen. Erweitern Sie sie oder verbinden Sie die Abfrage mit einer Zahlentabelle, wenn eine Eingabe mehr als fünf Teile enthalten kann. Der Zählerausdruck setzt eine nicht leere Eingabe voraus und behandelt jedes Komma als Trennzeichen. Legen Sie vor der Anwendung dieses Musters fest, wie Ihre Anwendung mit leeren Eingaben, wiederholten Trennzeichen oder Werten mit Kommas umgehen soll. Bei Daten, die häufig aufgeteilt werden, ist es in der Regel einfacher, jeden Wert in einer eigenen Zeile zu speichern und abzufragen.

Elemente aus einem JSON-Array auslesen

Enthält eine Spalte bereits ein gültiges JSON-Array, können Sie dessen Elemente mit JSON-Funktionen über ihren nullbasierten Index auslesen. Das ist ein Zugriff auf Array-Elemente und keine allgemeine Methode, um einfachen Text mit Trennzeichen aufzuteilen. Die folgenden Ausdrücke veranschaulichen die Elementpfade für ein Array wie ["apple","banana","cherry"]: JSON_UNQUOTE(JSON_EXTRACT(json_column, '$[0]')), JSON_UNQUOTE(JSON_EXTRACT(json_column, '$[1]')) und JSON_UNQUOTE(JSON_EXTRACT(json_column, '$[2]')). Sie geben der Reihe nach apple, banana und cherry ohne die JSON-Anführungszeichen zurück. Ersetzen Sie json_column durch den Namen Ihrer JSON-Spalte. Das Beispiel setzt gültiges JSON mit einem Array voraus, das diese Positionen enthält. Prüfen Sie, ob die Funktionen in der von Ihrem Server verwendeten MySQL-Version verfügbar sind.

Den passenden MySQL-Ansatz wählen

• Verwenden Sie SUBSTRING_INDEX(), wenn Sie einen Teil vor oder nach einem bekannten Vorkommen eines Trennzeichens benötigen.
• Verwenden Sie eine Zahlenfolge mit verschachtelten Aufrufen von SUBSTRING_INDEX(), wenn eine Abfrage jeden Teil mit Trennzeichen als eigene Zeile zurückgeben soll. Achten Sie darauf, dass die Zahlenfolge für die größte Eingabe lang genug ist, und legen Sie fest, wie leere Werte und wiederholte Trennzeichen behandelt werden sollen.
• Verwenden Sie die JSON-Extraktion, wenn die Daten bereits als gültiges JSON-Array vorliegen und Sie Elemente an bekannten Indizes benötigen. Sie wandelt eine beliebige durch Kommas getrennte Zeichenfolge nicht in ein JSON-Array um.

Wenn einzelne Werte regelmäßig gefiltert, verknüpft oder aktualisiert werden, sollten Sie erwägen, sie in separaten Zeilen zu speichern, statt die Zeichenfolge bei jeder Abfrage aufzuteilen.