Die SQL-Substring-Funktion in 5 Beispielen

Arbeiten Sie mit Textdaten in SQL? Wir erklären, wie man Werte von jeder beliebigen Stelle in einer Zeichenkette abrufen kann.

Wenn Sie an die Arbeit mit Daten in SQL denken, denken Sie wahrscheinlich zuerst an eine Datenbank voller Zahlen und an Ihren SQL-Code, der sehr ausgefallene Berechnungen durchführt. Aber auch Text ist Daten! Textdaten sind in Datenbanken sehr häufig zu finden. Man muss sie nicht nur extrahieren, sondern oft auch manipulieren. Die Funktionen, mit denen man dies tun kann, heißen Textfunktionen.

Für alle, die SQL-Funktionen üben wollen, empfehle ich unseren interaktiven Standard-SQL-Funktionen Kurs. Er enthält 211 Übungen und bringt Ihnen bei, wie man gängige Text-, Zahlen-, Datums- und Zeitfunktionen in SQL verwendet.

Eine der häufigsten Textfunktionen, die im Kurs behandelt werden, ist SUBSTRING(). In diesem Artikel finden Sie fünf Beispiele aus der Praxis, die die wichtigsten Verwendungszwecke dieser Funktion abdecken. Einige Beispiele können sich kompliziert anfühlen, wenn Sie mit den Textfunktionen nicht vertraut sind. Stellen Sie daher sicher, dass Sie den Standard-SQL-Funktionen Spickzettel oder einen Überblick über SQL-Textfunktionen zur Hand haben.

Was ist die Funktion SUBSTRING()?

SUBSTRING() ist eine Textfunktion, mit der Sie Zeichen aus einer Zeichenkette extrahieren können. Ihre Syntax lautet

SUBSTRING(expression, start, length)

Für das Argument expression geben Sie ein Stringliteral oder eine Spalte an, aus der Sie die Teilzeichenkette extrahieren möchten. Das Argument start ist eine ganze Zahl, die die numerische Position des Zeichens in der Zeichenkette angibt, an der die Teilzeichenkette beginnt. Das Argument length gibt, wie der Name schon sagt, die Länge der zurückzugebenden Teilzeichenkette an, die ein Integer-Wert ist.

Wie funktioniert SUBSTRING()?

Der Hinweis liegt im Namen der Funktion selbst. Eine Teilzeichenkette ist eine Zeichenkette innerhalb der Hauptzeichenkette. Daher extrahiert SUBSTRING() eine Teilzeichenkette, wie Sie sie in ihrem Argument angeben.

Das funktioniert folgendermaßen:

In der obigen Zeichenkette ist die Teilzeichenkette, die an Position 1 beginnt und eine Länge von drei Zeichen hat, ‘STR’.

Nachdem wir nun die Prinzipien kennen, möchte ich Ihnen einige Beispiele zeigen. Ich beginne natürlich mit dem einfachsten Beispiel!

Beispiel 1: Teilzeichenfolge aus einem String-Literal

Die Funktion SUBSTRING() gibt eine Teilzeichenkette aus einer beliebigen Zeichenkette zurück. Sie können die Zeichenkette explizit als Argument angeben, etwa so:

SELECT SUBSTRING('This is the first substring example', 9, 10) AS substring_extraction;

Dies bedeutet: Ich möchte eine Teilzeichenkette aus dem Text ‘This is the first substring example’ finden. Die Argumente besagen, dass die Teilzeichenkette am 9. Zeichen der Zeichenkette beginnt und dass ihre Länge 10 Zeichen beträgt.

Schauen wir uns an, was dieser Code zurückgibt:

Es gibt eine Spalte und eine Zeile. Die extrahierte Teilzeichenkette ist ‘the first’. Dies ist die einfachste Anwendung von SUBSTRING(); der Code verwendet nicht einmal Tabellen!

Die Tabelle "Mitarbeiter

Um Ihnen weitere interessante Beispiele zu zeigen, benötige ich einige Daten. Ich möchte Ihnen eine Tabelle namens employees.

Die Tabelle speichert Informationen über die Mitarbeiter eines imaginären Unternehmens Kooler in den folgenden Spalten:

• id - Die ID des Mitarbeiters.
• first_name - Der Vorname des Mitarbeiters.
• last_name - Der Nachname des Mitarbeiters.
• email - Die E-Mail des Mitarbeiters.
• job_title - Die Berufsbezeichnung des Mitarbeiters.
• department - Die Abteilung des Mitarbeiters.
• start_date - Das Anfangsdatum des Mitarbeiters bei Kooler.

Hier sind die ersten paar Zeilen, damit Sie einen Eindruck von den Daten bekommen:

Beispiel 2: Teilzeichenfolge aus einer Spalte

Wie Sie sich vorstellen können, ist das explizite Schreiben des String-Ausdrucks nicht die einzige Möglichkeit, SUBSTRING() zu verwenden. Sie können ihn auch auf eine Spalte einer Tabelle anwenden.

Hier ist ein solches Beispiel. Ich möchte die Initialen aller Mitarbeiter finden. Ich verwende die Spalte email, da ich weiß, dass die ersten beiden Buchstaben der E-Mail-Adresse die Initialen sind:

SELECT first_name,
	 last_name,
	 email,
	 SUBSTRING(email, 1, 2) AS employee_initials
FROM employees;

Ich gebe die Spalte email in der Funktion an. Wenn ich die ersten beiden Buchstaben der E-Mail-Adresse abrufe, beginnt die Teilzeichenkette mit dem ersten Zeichen und hat eine Länge von zwei Zeichen. Dies liefert das gewünschte Ergebnis:

Beispiel 3: Teilzeichenfolge ohne das Argument Länge

Sie können das Argument Länge in SUBSTRING() weglassen, und die Funktion funktioniert trotzdem. Ein gutes Beispiel ist, wenn Sie nur das Jahr des Beschäftigungsbeginns anzeigen möchten. Sie sehen, die Spalte start_date ist dafür etwas unfreundlich. Dieses Datum wird als Textdaten im Format MM/JJJJ geschrieben.

Glücklicherweise löst SUBSTRING() dieses Problem:

SELECT first_name,
	 last_name,
	 start_date,
	 SUBSTRING(start_date, 4) AS start_year
FROM employees;

Um das Jahr aus der Spalte start_date zu erhalten, genügt es, den Beginn der Teilzeichenkette zu definieren. In diesem Code beginnt die Teilzeichenkette mit dem vierten Zeichen. Da ich das Längenargument weglasse, ist die Länge der Teilzeichenkette so lang, wie es vom vierten Zeichen bis zum Ende der Zeichenkette ist. Auf diese Weise erhalte ich ganz einfach die Jahreszahl, wie Sie unten sehen:

Beispiel 4: POSITION() und CHARINDEX()

Zurück zur Arbeit mit E-Mails. Gemäß den Unternehmensrichtlinien ist der lokale Punkt einer E-Mail-Adresse (d. h. der Teil vor dem '@') auch der Benutzername des Mitarbeiters für die Anmeldung bei allen Geschäftsanwendungen. Sie müssen diesen Benutzernamen extrahieren. So geht's:

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

Die ersten beiden Argumente haben Sie bereits gesehen. Ich möchte eine Teilzeichenkette aus der Spalte email extrahieren, die mit dem ersten Zeichen der Zeichenkette beginnen soll. Nun ist aber die Länge der Teilzeichenkette für jeden Mitarbeiter unterschiedlich. Wie kann ich der Funktion sagen, dass sie alle Zeichen vor dem Zeichen "@" zurückgeben soll?

Ich verwende POSITION(), was CHARINDEX() in SQL Server oder MySQL entspricht. Sie sucht das angegebene Zeichen in der Zeichenkette und gibt dessen numerische Zeichenposition zurück. Die Länge der Teilzeichenkette, die den Benutzernamen des Mitarbeiters darstellt, ist also gleich POSITION('@' IN email)-1. Warum minus eins? Weil ich nicht möchte, dass das '@' im Benutzernamen des Mitarbeiters enthalten ist.

Dies ist das Ergebnis:

Beispiel 5: LENGTH() + POSITION()

Das letzte Beispiel zeigt Ihnen, wie Sie die Position eines Mitarbeiters aus den Daten ermitteln können. Da ich bei Kooler arbeite, weiß ich, wie die Stellenbezeichnungen gebildet werden: Zuerst kommt das Dienstalter des Mitarbeiters, dann die Abteilung, dann die Position. Zum Beispiel bedeutet "Junior Sales Assistant", dass der Mitarbeiter ein jüngeres Dienstalter hat, im Verkauf tätig ist und als Assistent arbeitet.

Mit SQL kann ich dies als Teilstring extrahieren:

SELECT first_name,
       last_name,
       job_title,
       SUBSTRING(job_title, LENGTH(job_title) - POSITION(' ' IN REVERSE(job_title))+2) AS position
FROM employees;

Dies ist ein weiteres Beispiel für das Weglassen des Längenarguments, wenn auch ein wenig komplexer. Wie immer gebe ich zuerst die String-Spalte an - in diesem Fall job_title. Danach muss ich irgendwie eine Teilzeichenkette finden, die nur aus dem letzten Wort des Jobtitels besteht.

Dazu verwende ich zunächst LENGTH(). Sie gibt die Länge der Zeichenkette in der Spalte job_title zurück. Das ist ein Anfang; es ist die Länge aller drei Wörter zusammen, einschließlich der Leerzeichen. Wenn ich davon irgendwie die Anzahl der Zeichen im letzten Wort abziehen könnte, dann hätte ich die Länge der ersten beiden Wörter, was mir dann den Anfang der gewünschten Teilzeichenkette liefern würde.

Diese ist ein wenig kompliziert, weil die Namen der verschiedenen Positionen unterschiedlich lang sind. Das einzige, was die Wörter trennt, ist das Leerzeichen. Um also die Länge des dritten Wortes in der Zeichenkette zu ermitteln, muss ich die Anzahl der Zeichen bis zum Leerzeichen zählen, aber von rechts.

Die Funktion POSITION() rettet wieder den Tag, aber dieses Mal in Kombination mit REVERSE(). Die Funktion REVERSE() kehrt den String-Ausdruck um, so dass aus "Junior Sales Assistant" "tnatsissA selaS roinuJ" wird. Das letzte Wort wird zum ersten; das Wort selbst wird ebenfalls umgekehrt, aber das spielt hier keine Rolle.

POSITION() findet die Position des Leerzeichens nach dem ersten Wort der umgekehrten Zeichenfolge. Dies entspricht der Stelle des Leerzeichens vor dem letzten Wort in der ursprünglichen (nicht umgedrehten) Zeichenfolge.

Puh! Wenn ich nun diese Zahl von der Gesamtlänge der ursprünglichen Zeichenkette abziehe, erhalte ich den Anfang der Teilzeichenkette, richtig?

Nun, nicht ganz! Die Differenz ergibt eine Teilzeichenkette, die den letzten Buchstaben des zweiten Wortes und das Leerzeichen vor dem letzten Wort enthält. Warum ist das so?

Aus zwei Gründen. Das Startargument der Funktion SUBSTRING() ist inklusive. Außerdem berechnet POSITION() die Position des Leerzeichens, nicht die Anzahl der Zeichen bis zum Leerzeichen. Ich muss also 2 addieren, um dieses Ergebnis zu erhalten:

Nachdem ich nun ein paar andere Funktionen vorgestellt habe, sollten Sie sich einige andere Textfunktionen ansehen, die für Sie nützlich sein könnten.

Erfahren Sie mehr über SUBSTRING () und die Arbeit mit Textdaten

Jetzt wissen Sie, wann und wie Sie SUBSTRING() verwenden können. Es ist Zeit zum Üben!

Es gibt noch andere Textfunktionen, nicht nur SUBSTRING(). Sie finden sie (und noch viel mehr!) im Standard-SQL-Funktionen Kurs.