Textfunktionen in SQL

Problem

Die Regaletiketten

Der Shop druckt Etiketten für die Regale: den Produktnamen und den Preis, etwa „Weißbrot (1,20 €)“. Der Name steht in einer Spalte und der Preis in einer anderen. Du brauchst eine Spalte mit dem fertigen Text, um sie direkt an den Drucker zu schicken.

Die Idee

Verbinden mit ||

|| verbindet Text. Die Stücke können Spalten sein, Text in Anführungszeichen oder Zahlen:

SQLDatenbank: Shop

SELECT name || ' (' || price || ' €)' AS label
FROM products
WHERE category_id = 3;

Fast. Die Zahl wird von selbst zu Text, aber in ihrer kürzesten Form: 1.2, mit Punkt und ohne die Null. Für ein Etikett braucht es zwei Ziffern und ein Komma.

printf('%.2f', price) schreibt die Zahl mit genau zwei Ziffern nach dem Punkt: 1.20. REPLACE(Text, was, womit) tauscht den Punkt gegen ein Komma:

SQLDatenbank: Shop

SELECT name || ' (' || REPLACE(printf('%.2f', price), '.', ',') || ' €)' AS label
FROM products
WHERE category_id = 3;

Funktionen lassen sich verschachteln: REPLACE bekommt den fertigen Text von printf.

Genauer, für Neugierige

printf ist eine Funktion von SQLite. Ab Version 3.38 heißt sie auch format. PostgreSQL hat für dasselbe to_char(price, 'FM990.00'), und MySQL hat FORMAT(price, 2). Wenn die Seite in mehreren Sprachen erscheint, ist es besser, die Zahl eine Zahl bleiben zu lassen und das Programm, das die Seite zeigt, sie formatieren zu lassen.

Probier es aus

Der E-Mail-Empfänger

E-Mail-Programme zeigen eine Adresse als „Maria Ivanova <maria.ivanova@example.com>“. Bau so eine Spalte für jeden Kunden und nenne sie recipient.

SQLDatenbank: Shop

SELECT name, email FROM customers;

Lösung

SELECT name || ' <' || email || '>' AS recipient FROM customers;

Sieh es dir an

Eine Stadt fehlt

Der Kurier will für jeden Kunden eine Zeile „Name, Stadt“:

SQLDatenbank: Shop

SELECT name, city, name || ', ' || city AS contact
FROM customers
WHERE id BETWEEN 10 AND 13;

1. Warum ist die Kontaktzeile für Boris Mitev NULL?Weil sein Name zu lang istWeil seine Stadt NULL ist und Text, der mit NULL verbunden wird, auch NULL istWeil || nur mit Zahlen funktioniert

Wenn eine Spalte NULL sein kann, kann auch der verbundene Text NULL sein. Wie du Worte an die Stelle der fehlenden Stadt setzt, kommt in der Lektion über CASE und COALESCE.

Die Idee

Teile des Textes

Die Textfunktionen, die du am häufigsten brauchst:

LENGTH(t): wie viele Zeichen er hat.
SUBSTR(t, ab, anzahl): ein Stück des Textes. Das Zählen beginnt bei 1.
INSTR(t, teil): die Position des Teils, oder 0, wenn er nicht vorkommt.
UPPER(t) und LOWER(t): Groß- und Kleinbuchstaben.
REPLACE(t, was, womit) und TRIM(t), das die Leerzeichen am Anfang und am Ende entfernt.

SQLDatenbank: Shop

SELECT name, LENGTH(name), SUBSTR(name, 1, 4), INSTR(name, ' ')
FROM products
WHERE category_id = 8;

SUBSTR und INSTR zusammen schneiden Text genau an der richtigen Stelle. Die Domain einer E-Mail ist alles nach dem @:

SQLDatenbank: Shop

SELECT email,
       INSTR(email, '@') AS at,
       SUBSTR(email, INSTR(email, '@') + 1) AS domain
FROM customers
WHERE city = 'Varna';

TRIM brauchst du, wenn die Daten aus einem Formular oder einer Tabellenkalkulation kommen und jemand ein Leerzeichen stehen gelassen hat:

SQLDatenbank: Shop

SELECT LENGTH('  Weißbrot '), LENGTH(TRIM('  Weißbrot '));

Probier es aus

Initialen

Die Kundenkarten brauchen die Initialen des Kunden: „M. I.“ für Maria Ivanova. Zeige den Namen und die Initialen in einer Spalte namens initials.

SQLDatenbank: Shop

SELECT name, SUBSTR(name, 1, 1) FROM customers;

Lösung

SELECT name, SUBSTR(name, 1, 1) || '. ' || SUBSTR(name, INSTR(name, ' ') + 1, 1) || '.' AS initials
FROM customers;

Sieh es dir an

UPPER und die Umlaute

Die Website will für jedes Produkt eine Adresse, etwa weißbrot: Kleinbuchstaben, ein Bindestrich statt Leerzeichen. REPLACE und LOWER erledigen das:

SQLDatenbank: Shop

SELECT name, LOWER(REPLACE(name, ' ', '-')) AS slug
FROM products
WHERE category_id = 3;

Hier funktioniert es, weil die Großbuchstaben in den Namen nur aus A–Z stammen. Das eingebaute UPPER und LOWER in SQLite ändern nur diese schlichten lateinischen Buchstaben. Ein Umlaut oder ein ganz anderes Alphabet wie Kyrillisch oder Griechisch bleibt, wie es ist. Ein „Ä“ würde LOWER nicht zu „ä“ machen, und aus dem „ä“ in „käse“ macht UPPER kein „Ä“:

SQLDatenbank: Shop

SELECT UPPER('kaese'), UPPER('käse');

Es gibt keinen Fehler, deshalb übersieht man es leicht. Dasselbe gilt für LIKE: Es ignoriert die Schreibweise nur bei den Buchstaben A bis Z.

Genauer, für Neugierige

PostgreSQL und MySQL mit UTF-8-Text ändern auch Umlaute und andere Alphabete richtig. SQLite hat eine Erweiterung (ICU), die UPPER, LOWER und LIKE jedes Alphabet beibringt, aber im Browser steht sie hier nicht zur Verfügung. Deshalb halten Programme, die SQLite benutzen, den Suchtext oft in einer eigenen Spalte, schon in Kleinbuchstaben, vom Programm berechnet.

Übung

Zwei Aufgaben

1. Auf ein Etikett passen 13 Zeichen. Welche Produkte haben einen längeren Namen? Zeige den Namen und seine Länge in einer Spalte namens length.

Die Produkte mit einem Namen über 13 Zeichen:

SQLDatenbank: Shop

Lösung

SELECT name, LENGTH(name) AS length FROM products WHERE LENGTH(name) > 13;

2. Das neue Anmeldesystem braucht einen Benutzernamen: den Teil der E-Mail vor dem @. Zeige die E-Mail und den Benutzernamen in einer Spalte namens username.

Die E-Mail und der Benutzername jedes Kunden:

SQLDatenbank: Shop

SELECT email, INSTR(email, '@') FROM customers;

Lösung

SELECT email, SUBSTR(email, 1, INSTR(email, '@') - 1) AS username FROM customers;

Test

Teste dich

1. Was ergibt 'Weiß' || ' ' || 'brot'?Weiß brotEinen Fehler: || bedeutet „oder“Weißbrot

2. Was liefert SUBSTR('Kaschkawal', 1, 4)?kawalaschKasc

3. Was liefert INSTR('Joghurt', ' ')?-1NULL0

4. Was ergibt UPPER('käse') in SQLite?KäSEEinen FehlerKÄSE

Textfunktionen

Cookies