Zum Inhalt springen
aviral gupta
Kontakt aufnehmen

// B2.3 · ca. 30 Min. · Einstieg

Ausdrücke und Textfunktionen

Nach dieser Lektion berechnen Sie neue Spalten aus den vorhandenen, formen Text um, finden Zeilen über ein Muster und geben jeder Zeile eine Bezeichnung.

Lektion 3 von 5 in B2 Abfragen auf einer Tabelle

Danach können Sie

  • Mit + - * / % und round() rechnen und mit :: oder CAST umwandeln, wo die Ganzzahldivision Nachkommastellen abschneiden würde
  • Text mit ||, upper, lower, length, substring, left, trim und replace bauen und ändern
  • Text mit LIKE und ILIKE vergleichen und Zeilen mit CASE WHEN beschriften

  1. Aufwärmen · Aufgabe 1 von 7

    Aufwärmen: Welcher Operator fügt in PostgreSQL zwei Zeichenketten zusammen, etwa Vor- und Nachname?

  2. Vorhersagen · Aufgabe 2 von 7

    Sagen Sie es voraus, bevor Sie weiterlesen. Was liefert dies?

    SELECT 7 / 2;

  3. Üben · Aufgabe 3 von 7

    Ergänzen Sie das Schlüsselwort, damit die Abfrage die Titel findet, die mit The beginnen.

    SELECT title FROM books WHERE title ____ 'The%';
    SELECT title FROM books WHERE title 'The%';

  4. Üben · Aufgabe 4 von 7

    Ordnen Sie jedem Ausdruck sein Ergebnis zu.

  5. Üben · Aufgabe 5 von 7

    Bringen Sie die Teile dieses CASE in eine Reihenfolge, in der Emma (1815) old und Dune (1965) modern heißt.

    1. 1.ELSE 'recent'
    2. 2.CASE
    3. 3.WHEN year < 1900 THEN 'old'
    4. 4.WHEN year < 1980 THEN 'modern'
    5. 5.END AS era

  6. Denksport · Aufgabe 6 von 7

    Knobelaufgabe. Die Titel sind Dune, Emma, Kindred, Beloved, The Hobbit und Neuromancer. Was liefert dies?

    SELECT count(*) FROM books
WHERE title LIKE '%e%';

  7. Anwenden · Aufgabe 7 von 7

    Mini-Aufgabe. Schreiben Sie für die Sci-Fi-Bücher, das älteste zuerst, eine Abfrage mit zwei Spalten: label, etwa Dune by Frank Herbert, und stock, das several copies zeigt, wenn ein Buch mehr als ein Exemplar hat, sonst one copy. Ändern Sie die Abfrage dann so, dass sie ILIKE auf den Titel anwendet, und sagen Sie voraus, welche Bücher sie findet.

    Prüfen Sie Ihr Ergebnis anhand dieser Liste

Selbst programmieren

Lesen Sie das ausgearbeitete Beispiel und lösen Sie dann die Übungen. Ihr Code läuft in Ihrem Browser oder auf Ihrem Computer und wird nie hochgeladen.

Ausgearbeitetes Beispiel

Zahlen, Text und Bezeichnungen

setup.sql lädt die Bibliothek aus Modul B1. main.sql rechnet mit Ganzzahlen und einem Cast, baut eine Bezeichnung aus zwei Spalten und gibt jedem Buch mit CASE eine Epoche, für die Titel mit einem kleinen e. Auf Ihrem Computer: psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql.

main.sql

-- 1. Arithmetic: integer division, remainder, a cast, rounding.
SELECT 7 / 2 AS int_div,
       7 % 2 AS remainder,
       7::numeric / 2 AS exact,
       round(7::numeric / 3, 2) AS rounded;

-- 2. Text: glue strings together and change them.
SELECT title || ' (' || year || ')' AS label,
       upper(genre) AS shelf,
       length(title) AS letters
FROM books
WHERE genre = 'sci-fi'
ORDER BY year;

-- 3. A pattern and a label per row.
SELECT title,
       CASE WHEN year < 1900 THEN 'old'
            WHEN year < 1980 THEN 'modern'
            ELSE 'recent'
       END AS era
FROM books
WHERE title LIKE '%e%'
ORDER BY year;

setup.sql

CREATE TABLE books (
  id integer,
  title text,
  author text,
  year integer,
  genre text,
  copies integer
);

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

CREATE TABLE members (
  id integer,
  name text,
  email text,
  city text,
  joined date
);

INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

Ausführen mit

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

Ausgabe

 int_div | remainder |       exact        | rounded
---------+-----------+--------------------+---------
       3 |         1 | 3.5000000000000000 |    2.33
(1 row)

       label        | shelf  | letters
--------------------+--------+---------
 Dune (1965)        | SCI-FI |       4
 Kindred (1979)     | SCI-FI |       7
 Neuromancer (1984) | SCI-FI |      11
(3 rows)

    title    |  era
-------------+--------
 The Hobbit  | modern
 Dune        | modern
 Kindred     | modern
 Neuromancer | recent
 Beloved     | recent
(5 rows)
  • 7 / 2 ist 3: Beide sind Ganzzahlen. Nach dem Cast ist 7::numeric / 2 exakt.
  • year ist eine Ganzzahl, doch || macht Text daraus, weil die andere Seite Text ist.
  • upper(genre) ändert nur das Ergebnis; in der Tabelle steht weiterhin sci-fi in Kleinbuchstaben.
  • Emma fehlt im letzten Ergebnis: LIKE '%e%' sucht ein kleines e, und Emma hat nur ein großes E.
Ändern und ausführen

Tab rückt ein, Umschalt+Tab rückt aus. Um den Editor mit der Tastatur zu verlassen, drücken Sie Esc und dann Tab.

Beim ersten Ausführen lädt Ihr Browser PostgreSQL herunter (bis zu 10.1 MB) und speichert es im Cache. Jeder Lauf beginnt mit einer leeren Datenbank. Ihr Code bleibt auf Ihrem Gerät.

Übungen

Übung 1 von 2

Regalcodes

Die Bibliothek möchte für jedes Buch einen Regalcode: die ersten drei Buchstaben des Titels in Großbuchstaben, ein Bindestrich und das Jahr, etwa DUN-1965. Schreiben Sie eine Abfrage mit den Spalten title und code, nach id sortiert.

Tab rückt ein, Umschalt+Tab rückt aus. Um den Editor mit der Tastatur zu verlassen, drücken Sie Esc und dann Tab.

Beim ersten Ausführen lädt Ihr Browser PostgreSQL herunter (bis zu 10.1 MB) und speichert es im Cache. Jeder Lauf beginnt mit einer leeren Datenbank. Ihr Code bleibt auf Ihrem Gerät.

Hinweise
  1. Hinweis 1

    left(title, 3) nimmt die ersten drei Zeichen.

  2. Hinweis 2

    Umschließen Sie es mit upper(…) für Großbuchstaben und fügen Sie die Teile mit || zusammen.

  3. Hinweis 3

    upper(left(title, 3)) || '-' || year AS code

Eine Lösung zeigen

Ein möglicher Lösungsweg. Ihrer kann anders aussehen und trotzdem alle Prüfungen bestehen.

SELECT title,
       upper(left(title, 3)) || '-' || year AS code
FROM books
ORDER BY id;
Auf dem eigenen Computer ausführen

Installieren Sie PostgreSQL 18 oder neuer. Speichern Sie diese Dateien in einem Ordner, öffnen Sie dort ein Terminal und führen Sie die Befehle unten aus.

main.sql

-- setup.sql has loaded books and members.
SELECT title, year FROM books ORDER BY id;

test.sql

-- test: Dune erhält den Code DUN-1965
SELECT count(*) = 1 FROM books WHERE upper(left(title, 3)) || '-' || year = 'DUN-1965';

-- test: Die Abfrage listet jedes Buch mit seinem Regalcode
-- output:
--     title    |   code
-- -------------+----------
--  Dune        | DUN-1965
--  Emma        | EMM-1815
--  Kindred     | KIN-1979
--  Beloved     | BEL-1987
--  The Hobbit  | THE-1937
--  Neuromancer | NEU-1984
-- (6 rows)

setup.sql

CREATE TABLE books (
  id integer,
  title text,
  author text,
  year integer,
  genre text,
  copies integer
);

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

CREATE TABLE members (
  id integer,
  name text,
  email text,
  city text,
  joined date
);

INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

psql führt die Dateien auf Ihrem eigenen PostgreSQL-Server aus. Nehmen Sie eine leere Datenbank, die Sie wegwerfen können: Hängen Sie -d und ihren Namen an den Befehl an.

Programm ausführen:

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

Für die Prüfungen gibt es auf Ihrem Computer noch keinen Befehl. Sie stehen in test.sql: Jede Prüfabfrage gibt t aus, wenn sie besteht.

Übung 2 von 2

Anteil an den Exemplaren

Die Bibliothek hat insgesamt 9 Exemplare. Der Starter soll den Anteil jedes Buchs daran in Prozent zeigen, doch jede Zeile zeigt 0. Korrigieren Sie percent, sodass der Anteil mit einer Nachkommastelle erscheint (Dune: 22.2). Fügen Sie dann eine Spalte stock hinzu, die several zeigt, wenn ein Buch zwei oder mehr Exemplare hat, sonst single. Behalten Sie die Sortierung nach id.

Tab rückt ein, Umschalt+Tab rückt aus. Um den Editor mit der Tastatur zu verlassen, drücken Sie Esc und dann Tab.

Beim ersten Ausführen lädt Ihr Browser PostgreSQL herunter (bis zu 10.1 MB) und speichert es im Cache. Jeder Lauf beginnt mit einer leeren Datenbank. Ihr Code bleibt auf Ihrem Gerät.

Hinweise
  1. Hinweis 1

    copies / 9 teilt zwei Ganzzahlen: 2 / 9 ist 0, und 0 * 100 bleibt 0.

  2. Hinweis 2

    Multiplizieren Sie zuerst mit 100.0, dann rechnet der Ausdruck mit numeric, und runden Sie mit round(…, 1).

  3. Hinweis 3

    CASE WHEN copies >= 2 THEN 'several' ELSE 'single' END AS stock

Eine Lösung zeigen

Ein möglicher Lösungsweg. Ihrer kann anders aussehen und trotzdem alle Prüfungen bestehen.

-- Each book’s share of the 9 copies, in percent.
SELECT title,
       round(copies * 100.0 / 9, 1) AS percent,
       CASE WHEN copies >= 2 THEN 'several' ELSE 'single' END AS stock
FROM books
ORDER BY id;
Auf dem eigenen Computer ausführen

Installieren Sie PostgreSQL 18 oder neuer. Speichern Sie diese Dateien in einem Ordner, öffnen Sie dort ein Terminal und führen Sie die Befehle unten aus.

main.sql

-- Each book’s share of the 9 copies, in percent.
SELECT title,
       copies / 9 * 100 AS percent
FROM books
ORDER BY id;

test.sql

-- test: Die Abfrage listet jeden Anteil mit einer Nachkommastelle und eine Bestandsangabe
-- output:
--     title    | percent |  stock
-- -------------+---------+---------
--  Dune        |    22.2 | several
--  Emma        |    11.1 | single
--  Kindred     |    11.1 | single
--  Beloved     |    11.1 | single
--  The Hobbit  |    33.3 | several
--  Neuromancer |    11.1 | single
-- (6 rows)

setup.sql

CREATE TABLE books (
  id integer,
  title text,
  author text,
  year integer,
  genre text,
  copies integer
);

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

CREATE TABLE members (
  id integer,
  name text,
  email text,
  city text,
  joined date
);

INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

psql führt die Dateien auf Ihrem eigenen PostgreSQL-Server aus. Nehmen Sie eine leere Datenbank, die Sie wegwerfen können: Hängen Sie -d und ihren Namen an den Befehl an.

Programm ausführen:

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

Für die Prüfungen gibt es auf Ihrem Computer noch keinen Befehl. Sie stehen in test.sql: Jede Prüfabfrage gibt t aus, wenn sie besteht.

Häufige Fehler

+ zum Zusammenfügen von Text

SELECT title + ' (' + year + ')'
FROM books;

Was psql ausgibt

ERROR:  operator does not exist: text + unknown

Warum, und die Lösung

In PostgreSQL addiert + Zahlen. Für eine Textspalte und einen Wert in Anführungszeichen (vom Typ unknown, bis PostgreSQL entscheidet) gibt es kein +, und genau das sagt die Meldung. Fügen Sie Text mit || zusammen: title || ' (' || year || ')'.

Durch einen Wert teilen, der null sein kann

SELECT title, 100 / (copies - 1) AS per_extra_copy
FROM books;

Was psql ausgibt

ERROR:  division by zero

Warum, und die Lösung

Emma hat ein Exemplar, copies - 1 ist in ihrer Zeile also 0, und PostgreSQL bricht die ganze Abfrage ab, statt eine Zeile ohne Wert zu liefern. Halten Sie diese Zeilen mit WHERE copies > 1 fern oder behandeln Sie sie mit CASE WHEN copies > 1 THEN 100 / (copies - 1) END.

CASE-Ergebnisse verschiedener Typen

SELECT title,
       CASE WHEN copies > 1 THEN 'many' ELSE copies END AS stock
FROM books;

Was psql ausgibt

ERROR:  invalid input syntax for type integer: "many"

Warum, und die Lösung

Alle Ergebnisse eines CASE brauchen einen Typ. copies ist eine Ganzzahl, also versucht PostgreSQL, 'many' als Ganzzahl zu lesen, und scheitert. Machen Sie jeden Zweig zu Text: ELSE copies::text, oder besser ein Wort wie ELSE 'one'.

PostgreSQL im Browser: PGlite 0.5.8 (PostgreSQL 18.3), Apache-2.0 und PostgreSQL License. Lizenz und Quellcode

Abschlussquiz

5 Fragen, ohne Hinweise. Ab 80 % ist die Lektion abgeschlossen.

Erledigen Sie zuerst alle Aufgaben oben, um das Abschlussquiz freizuschalten.

Problem melden

Etwas ist falsch oder unklar? Beschreiben Sie es kurz, dann wird es geprüft und korrigiert.

#

Mindestens 20 Zeichen.

Nur, wenn Sie eine Antwort wünschen.

Kernideen

Rechnen, und die Ganzzahlfalle

Die Select-Liste kann rechnen: copies * 2, year + 100, 2026 - year. Die Operatoren sind + - * / und %, der Rest: 7 % 2 ist 1. Die Division zweier Ganzzahlen ergibt eine Ganzzahl und schneidet den Rest ab: 7 / 2 ist 3, nicht 3.5. Sobald eine Seite numeric ist, wird exakt gerechnet: 7 / 2.0 und 7::numeric / 2 ergeben beide 3.5000000000000000. :: ist PostgreSQLs Cast (Typumwandlung); CAST(7 AS numeric) ist die Standardschreibweise desselben. round(x, 2) rundet einen numeric-Wert auf zwei Stellen. Eine berechnete Spalte hat keinen eigenen Namen, psql zeigt darum ?column?; geben Sie ihr mit AS einen.

Text: zusammenfügen und ändern

|| fügt Zeichenketten zusammen: title || ' (' || year || ')' ergibt Dune (1965). Eine Zahl wird dabei von selbst zu Text, solange eine Seite Text ist. + fügt keinen Text zusammen: Bei einer Textspalte scheitert es mit operator does not exist: text + integer. Die Textfunktionen liefern einen neuen Wert und lassen die Tabelle unverändert: upper('Dune') ist DUNE, lower('Dune') ist dune, length('Dune') ist 4, left('Neuromancer', 5) ist Neuro, substring('Neuromancer' from 5 for 3) ist oma, trim(' Emma ') ist Emma und replace('sci-fi', '-', ' ') ist sci fi. Funktionen lassen sich verschachteln: upper(left(title, 3)).

Muster mit LIKE, Bezeichnungen mit CASE

title LIKE 'D%' ist wahr für Titel, die mit D beginnen: % steht für beliebig viele Zeichen, _ für genau eines. Das Muster muss die ganze Zeichenkette treffen, LIKE 'Dun' passt also nur auf Dun selbst; um ein Wort irgendwo zu finden, schreiben Sie '%wort%'. LIKE unterscheidet Groß- und Kleinschreibung, ILIKE ist dasselbe ohne diesen Unterschied: 'The Hobbit' ILIKE 'the%' ist wahr. CASE WHEN year < 1900 THEN 'old' WHEN year < 1980 THEN 'modern' ELSE 'recent' END gibt jeder Zeile eine Bezeichnung. Die Bedingungen werden der Reihe nach geprüft, und die erste wahre gewinnt; setzen Sie die engste also zuerst. Alle Ergebnisse müssen zu einem Typ passen: Text und Zahl gemischt scheitert.

Quellen

Zuletzt geprüft am 30. September 2026