Ein kompletter Leitfaden für die Arbeit mit Teilzeichenketten in SQL

In diesem Artikel geht es um die Arbeit mit Teilzeichenketten in SQL. Dazu müssen Sie mehrere Ansätze kennenlernen, und wir zeigen Ihnen deren Besonderheiten.

Um mit Teilzeichenketten in SQL arbeiten zu können, müssen wir zunächst wissen, was eine Zeichenkette ist. In der Programmierung ist ein String jede Art von Textdaten (ein Titel, eine Beschreibung, ein Name). Eine Zeichenkette besteht aus einer beliebigen Anzahl und Art von Zeichen. In einer SQL-Datenbank werden Strings normalerweise als CHAR- oder VARCHAR-Datentypen gespeichert.

Eine Teilzeichenkette ist ein Teil einer Zeichenkette. Mit anderen Worten, eine Teilzeichenkette ist ein kleinerer Teil der Zeichenfolge. Bei der Zeichenkette "SQL-Kurs" wäre "SQL" eine Teilzeichenkette für diese Zeichenkette. Das folgende Bild veranschaulicht dies:

Beachten Sie, dass das Bild (und eine Zeichenkette) Leerzeichen nicht ignoriert; Leerzeichen sind auch Zeichen in einer Zeichenkette.

Bereiten Sie sich auf die Arbeit mit SQL-Substrings vor

Die Kenntnis der SQL-Textfunktionsfamilie ist für die Arbeit mit Strings unerlässlich. Auch wenn wir die Beispiele für Substrings so einfach wie möglich halten, werden wir Textfunktionen benötigen. Die Verwendung dieser Funktionen erfordert oft die Kenntnis anderer SQL-Konzepte, wie JOINs, das Gruppieren von Daten, das Filtern von Daten und die Verwendung von Unterabfragen. Sie können alle diese Konzepte in unserem SQL-Praxis Kurs. Es gibt acht Übungskurse mit insgesamt mehr als 950 Programmieraufgaben, so dass Ihnen am Ende dieses Kurses die Finger wehtun werden :) Aber das ist ja gerade der Sinn der Sache, denn Übung ist wirklich der einzige Weg, um SQL flüssig zu beherrschen.

Wir werden hier den gleichen Ansatz verwenden und Ihnen so viele Beispiele zeigen, wie dieser Artikel zulässt. Wenn Sie in SQL eine Teilzeichenkette aus einer Zeichenkette extrahieren möchten, scheint die Textfunktion mit dem auffälligen Namen SUBSTRING() eine naheliegende Wahl zu sein. Sie ist jedoch nicht die einzige Wahl! Es gibt auch andere, wie den Operator LIKE und die Textfunktionen LEFT() und RIGHT().

Auch wenn dies einfach erscheint, ist die Arbeit mit SQL-Substrings nicht immer einfach. Sie müssen die Feinheiten jeder Funktion, die Sie anwenden wollen, kennen, wissen, wie die Indexierung in SQL funktioniert und wie SQL mit Textdaten umgeht. Viele Datenexperten werden bestätigen, dass die Arbeit mit Teilzeichenketten in SQL zu einer schmerzhaften und frustrierenden Erfahrung werden kann!

Wir werden dafür sorgen, dass es für Sie nicht schmerzhaft wird. Fangen wir an.

Beispiel-Datensatz

Wir arbeiten mit der Tabelle clients. Wie der Name schon sagt, handelt es sich um eine Liste der Kunden des Unternehmens.

Sie können die Tabelle mit Hilfe dieser Abfrage erstellen. Beachten Sie, dass das Geburtsdatum in einem Textdatenformat gespeichert ist.

Beginnen wir mit der Extraktion von Teilstrings!

Prüfen, ob eine Zeichenkette eine Teilzeichenkette in SQL enthält

Eine gängige Methode, um in SQL zu prüfen, ob ein Text eine bestimmte Teilzeichenkette enthält, ist die Verwendung der Operatoren LIKE oder ILIKE. Sie werden zusammen mit der WHERE Klausel verwendet, um Daten zu filtern. Beide Operatoren finden Zeichenketten mit einer Teilzeichenkette , die der in ihrem Muster definierten Zeichenkette entspricht . Der einzige Unterschied besteht darin, dass bei LIKE zwischen Groß- und Kleinschreibung unterschieden wird, während dies bei ILIKE nicht der Fall ist.

LIKE und ILIKE verwenden, um zu prüfen, ob ein Text eine Teilzeichenkette enthält

Ihr Kollege erzählt Ihnen, dass ein Kunde mit einer Nachricht für Sie angerufen hat. Da der Kollege in Eile war, hat er vergessen, sich den Namen des Kunden zu notieren. Der Name des Kunden war Isaac oder Dave oder so ähnlich.

Sie könnten versuchen, den Kunden namens Isaac oder Dave aufzuspüren, indem Sie diese Abfrage schreiben:

SELECT *
FROM clients
WHERE first_name LIKE 'Dave' OR first_name LIKE 'Isaac';

In dieser Abfrage wählen Sie alle Spalten der Tabelle aus. Dann verwenden Sie die WHERE Klausel und den LIKE Operator. Sie setzen die Namen 'Dave' und 'Isaac' in einfache Anführungszeichen im LIKE Operator. Die Bedingung in der WHERE Klausel kann wie folgt gelesen werden: "Gib alle Kunden mit Dave oder Isaac als Vornamen zurück".

Führen wir den Code aus und sehen wir, dass es absolut nichts gibt! Der Code gibt keine Zeilen zurück. Blöder Code! Wenn wir uns die Tabelle ansehen, sehen wir, dass es Kunden namens Isaac Guardiola und Dave Trotter gibt:

Warum funktioniert es also nicht? Die obige Abfrage nimmt an, dass die Teilzeichenkette mit "Dave" oder "Isaac" beginnt. Anhand der Tabelle können wir sehen, dass die Zeichenfolge tatsächlich mit der Anrede beginnt.

Damit diese Abfrage funktioniert, sollten wir den Platzhalter '%' mit LIKE verwenden. Dieser Platzhalter wird für die Suche nach einer Teilzeichenkette am Anfang, am Ende und irgendwo dazwischen verwendet. Es hängt alles davon ab, wo Sie den Platzhalter positionieren. Hier ist ein kurzer Überblick über die Verwendung dieses Platzhalters.

Lassen Sie uns die Abfrage neu schreiben:

SELECT *
FROM clients
WHERE first_name LIKE '%Dave%' OR first_name LIKE '%Isaac%';

Wir haben gelernt, dass die Platzhalter vor und nach der gesuchten Teilzeichenkette bedeuten, dass die Abfrage überall in der Zeichenkette nach dieser Teilzeichenkette suchen wird. So können wir die Anrede vor jedem Namen umgehen.

Hier ist die Ausgabe:

Um Ihnen ein weiteres Beispiel für die Verwendung von '%' mit LIKE zu geben, können Sie auch nach allen Herren in unserer Kundenliste suchen.

Dazu setzen Sie den Platzhalter nach der Teilzeichenkette, die Sie suchen, da die Teilzeichenkette mit "Mr." beginnt:

SELECT *
FROM clients
WHERE first_name LIKE 'Mr.%';

Hier ist die Ausgabe.

Sie haben wahrscheinlich bemerkt, dass wir die Teilzeichenketten in LIKE genau so schreiben, wie sie in der Tabelle in Bezug auf die Großschreibung angegeben sind. Das liegt daran, dass der Operator LIKE zwischen Groß- und Kleinschreibung unterscheidet.

Wenn Sie das vermeiden wollen oder sich nicht sicher sind, wie die Daten formatiert sind, können Sie den Operator ILIKE verwenden. Da die Groß- und Kleinschreibung nicht beachtet wird, können Sie die Teilzeichenkette so schreiben, wie Sie wollen:

SELECT *
FROM clients
WHERE first_name ILIKE '%dave%' 
OR first_name ILIKE '%iSAaC%';

Die Ausgabe ist die gleiche wie bei LIKE:

Mehr über LIKE (und NOT LIKE) erfahren Sie in diesem Artikel.

Die Funktion SUBSTRING()

SUBSTRING() ist eine SQL-Funktion, die zum Extrahieren von Teilstrings aus einer Zeichenkette verwendet wird. Sie ermöglicht es Ihnen, die Zeichenkette anzugeben, aus der Sie die Teilzeichenkette extrahieren möchten. Sie definieren die Teilzeichenkette durch Angabe ihrer Anfangsposition innerhalb der Zeichenkette und ihrer Länge.

Hier ist die Syntax der Funktion:

SUBSTRING(string, start_position, length)

Wie jede SQL-Funktion nimmt SUBSTRING() Argumente in Klammern entgegen. Zwei Argumente sind obligatorisch:

• string - Die zu durchsuchende Zeichenkette; sie kann eine Konstante oder eine Spalte/ein Ausdruck sein.
• start_position - Ein ganzzahliger Wert, der die Position (die Ordnungszahl des Zeichens) definiert, an der die Teilzeichenkette beginnt, d. h. die Position des ersten Zeichens der Teilzeichenkette.

Das dritte Argument ist length, das optional ist. Es definiert die Länge einer Teilzeichenkette als die Anzahl der Zeichen, die sie enthält. Wenn dieses Argument weggelassen wird, ist die Teilzeichenkette der Rest der Zeichenkette, gezählt ab der Startposition.

Um dies zu verdeutlichen, sehen wir uns einige Beispiele an.

SUBSTRING() verwenden, um eine Teilzeichenkette aus einer konstanten Zeichenkette zu extrahieren

Eine konstante Zeichenkette ist jede Zeichenkette, die Sie in einfachen Anführungszeichen als erstes Argument in SUBSTRING() schreiben.

Die konstante Zeichenkette im folgenden Code ist zum Beispiel 'SQL-Praxis Course', und wir wollen 'Practice Course' extrahieren.

SELECT SUBSTRING ('SQL-Praxis Course', 5) 
AS substring_extract

Die ganze Zahl 5 ist das Argument start_position. Das bedeutet, dass die Teilzeichenkette mit dem fünften Zeichen der Zeichenkette beginnt.

Warum wird 5 gewählt, wenn nur drei Buchstaben ("SQL") vor der Teilzeichenkette stehen? Sollte die Teilzeichenkette nicht an der vierten Stelle beginnen? Seien Sie vorsichtig: Das Leerzeichen zählt auch als Zeichen in einer Zeichenkette!

Da wir das Argument length weggelassen haben, gibt der Code den Rest der Zeichenkette als Teilzeichenfolge zurück. Mit anderen Worten: Der Code gibt dieses Ergebnis zurück:

SUBSTRING() verwenden, um eine Teilzeichenkette aus einer Spalte zu extrahieren

Anstelle eines konstanten Wertes können Sie auch eine Spalte angeben, aus der Sie eine Teilzeichenkette extrahieren möchten.

Wir wollen den Vor- und Nachnamen des Kunden anzeigen, aber ohne die Anrede (Herr/Frau/Mx.):

SELECT SUBSTRING (first_name, 5) AS first_name,
	 last_name
FROM clients;

Wir verwenden wieder die Funktion SUBSTRING(). Das erste Argument ist der Name der Spalte, der first_name lautet. Das zweite Argument ist die Anfangsposition der Teilzeichenkette, die fünf ist. Denken Sie daran, die Leerzeichen zu zählen! Nach der Anrede kommt eines, also besteht "Mr." aus vier Zeichen. Das fünfte Zeichen ist der erste Buchstabe des Namens des Kunden.

Wir haben die Länge der Teilzeichenkette weggelassen, und die zweite ausgewählte Spalte ist der Nachname des Kunden, so dass der Code dies zurückgibt:

SUBSTRING() verwenden, um eine Teilzeichenkette mit einer bestimmten Länge zu extrahieren

Wir werden nun aufhören, das dritte Argument SUBSTRING() zu vermeiden. In diesem Beispiel zeigen wir, wie man das Geburtsdatum und den Geburtsmonat der Kunden extrahiert:

SELECT first_name,
	 last_name,
	 SUBSTRING (date_of_birth, 1, 6) AS birthday
FROM clients;

Nachdem wir die Vor- und Nachnamen der Kunden ausgewählt haben, verwenden wir die Funktion SUBSTRING(). Wir verwenden die Spalte date_of_birth, um den Geburtstag des Kunden (Monat und Tag) zu extrahieren.

Nachdem wir die Spalte als Argument übergeben haben, geben wir die Startposition an. Dies ist die Nummer 1, was bedeutet, dass die Teilzeichenkette ab dem ersten Zeichen der Zeichenkette extrahiert wird. Die Zahl 6 bedeutet, dass die Teilzeichenkette aus sechs Zeichen bestehen wird: zwei Zeichen für einen Tag, ein Punkt, zwei Zeichen für einen Monat und ein weiterer Punkt.

Werfen Sie einen Blick auf das Ergebnis:

Alle Kunden sind da, zusammen mit ihren Geburtstagen, die wir extrahiert haben, wie wir es wollten. Diesmal stört es uns nicht, dass die Anrede angezeigt wird.

SUBSTRING() zusammen mit anderen Funktionen verwenden, um einen Teilstring-Index zu finden

Es gibt Funktionen, die für die Suche nach einem Index innerhalb einer Zeichenkette verwendet werden können. In MySQL und PostgreSQL heißt diese Funktion POSITION(), in SQL Server heißt sie CHARINDEX(). Sie können die gesuchte Teilzeichenkette angeben, und diese Funktionen geben ihre Position innerhalb der Zeichenkette zurück.

Diese Funktionen sind in Kombination mit SUBSTRING() äußerst hilfreich. Lassen Sie uns zuerst POSITION() besprechen, dann sehen wir uns ein Beispiel mit CHARINDEX() an.

Mit der Funktion POSITION() können Sie die Teilzeichenkette angeben, und sie findet ihre Anfangsposition. Verwenden wir sie, um den Benutzernamen eines jeden Kunden aus seiner E-Mail-Adresse zu extrahieren:

SELECT first_name,
	 last_name,
	 email,
	 POSITION('@' IN email) AS at_position,
	 SUBSTRING(email, 1, POSITION('@' IN email)-1) AS username
FROM clients;

Oben verwenden wir POSITION(), um das "@" in den E-Mail-Adressen der einzelnen Benutzer zu finden. Das brauchen Sie, weil alles, was vor diesem Zeichen kommt, der Name des Benutzers ist. Die Syntax für POSITION() ist einfach: Geben Sie einfach das Suchzeichen in einfachen Anführungszeichen an, dann folgen das Schlüsselwort IN und der Name der Spalten, in denen Sie suchen möchten.

Diese Spalte gibt nur die Position des Zeichens zurück. Wir müssen sie in SUBSTRING() einbetten, um den Benutzernamen zu erhalten. Die ersten beiden Argumente in SUBSTRING() sind bekannt: Wir suchen in der Spalte email, und wir wollen, dass unsere Teilzeichenkette (ein Benutzername) mit dem ersten Zeichen der Zeichenkette beginnt.

Das dritte Argument in SUBSTRING() ist die Länge der Teilzeichenkette. Die Länge des Benutzernamens ist die Anzahl der Zeichen vor und ohne '@'. Mit anderen Worten, die Länge des Benutzernamens ist die Position von '@' minus eins. Warum minus? Weil sonst '@' Teil des Benutzernamens wäre.

Wir können das Ergebnis überprüfen. In "iguardiola@gmail.com" befindet sich das Symbol "@" an Position 11, wie in der Spalte at_position zu sehen ist. Die Spalte Benutzername zeigt die Teilzeichenkette vor '@', die für diesen Kunden tatsächlich 'iguardiola' ist. Sie können die übrigen Ergebnisse auf die gleiche Weise überprüfen.

Derselbe Code in SQL Server sollte die Funktion CHARINDEX() verwenden:

SELECT first_name,
	 last_name,
	 email,
	 CHARINDEX('@', email) AS at_position,
	 SUBSTRING(email, 1, CHARINDEX('@', email)-1) AS username
FROM clients;

Die Logik ist ähnlich wie bei der Funktion POSITION(): Sie geben die Teilzeichenkette an, nach der Sie suchen, und dann die Spalte, wobei die Argumente durch ein Komma getrennt sind.

Die Codeausgabe ist die gleiche.

Wir sind jetzt mit SUBSTRING() fertig. Wenn Sie aber noch mehr Übung brauchen, finden Sie hier fünf weitere Beispiele für SUBSTRING().

LEFT() und RIGHT() in SQL

Eine weitere Möglichkeit, eine Teilzeichenkette zu extrahieren, ist die Verwendung der Funktionen LEFT() oder RIGHT().

LEFT() Die Funktion LEFT() extrahiert die Teilzeichenkette von links und ermöglicht es uns, die Länge der Teilzeichenkette zu definieren. Die Funktion RIGHT() macht dasselbe, aber von der rechten Seite.

LEFT() und RIGHT() verwenden, um eine Teilzeichenkette zu extrahieren

Verwenden wir diese beiden Funktionen, um einige Teilstrings zu extrahieren: die ersten drei Buchstaben eines Nachnamens und das Geburtsjahr.

Hier ist der Code.

SELECT first_name,
	 last_name,
	 LEFT(last_name, 3) AS last_name_substring,
	 RIGHT(date_of_birth, 4) AS year_of_birth
FROM clients;

Wir verwenden die Funktion LEFT(), um die ersten drei Buchstaben des Nachnamens zu ermitteln. Sie beginnt von links und nimmt die angegebene Anzahl von Zeichen, um eine Teilzeichenkette zu erstellen. Die Syntax ist einfach: Wir geben den Spaltennamen und dann die Länge der Teilzeichenkette an.

RIGHT() wird verwendet, um das Geburtsjahr zu finden. Es wird von rechts begonnen und die angegebene Anzahl von Zeichen benötigt, um eine Teilzeichenkette zu erstellen. Hier ist das erste Argument die Spalte date_of_birth. Dann zählen wir die Anzahl der gewünschten Zeichen von rechts, also vier - dies ergibt das Geburtsjahr.

Da LEFT() und RIGHT() im Grunde genommen Spiegelbilder voneinander sind, hätten wir problemlos eine dieser beiden Funktionen verwenden können, um die beiden benötigten Teilzeichenfolgen zu finden.

Hier ist die Ausgabe der obigen Abfrage:

Sie zeigt die ersten drei Buchstaben des Nachnamens und das Geburtsjahr an.

Werden Sie derjenige, der alle (Teil-)Zeichenfolgen in SQL abfragt

Als Datenanalytiker wird von Ihnen erwartet, dass Sie alle (Unter-)Stränge selbst ziehen. Einer der Tricks, um dies zu erreichen, besteht darin, verschiedene Möglichkeiten der Arbeit mit Teilzeichenketten zu kennen, wie die, die wir Ihnen hier gezeigt haben.

Wenn Sie verschiedene Funktionen für den Umgang mit Teilzeichenketten kennen, können Sie diejenige auswählen, die für Ihr Problem am besten geeignet ist. So erhalten Sie nicht nur das gewünschte Ergebnis, sondern auch einen effizienteren Code mit weniger Zeilen.

Um dieses Niveau zu erreichen, müssen Sie viel mit Strings arbeiten und alle genannten Funktionen täglich verwenden. Mit anderen Worten: Sie müssen Code schreiben. Wenn Sie dafür eine sichere Umgebung brauchen, bietet unser SQL-Praxis Kurs bietet genau das.

Viel Glück dabei, der Meister der SQL-Substrings zu werden!