Einführung in SQL: Funktionen (2)
Aus Wikibooks
Dieses Kapitel behandelt weitere Skalarfunktionen in Ergänzung zu den grundlegenden Funktionen.
Inhaltsverzeichnis |
[Bearbeiten] Allgemeine Hinweise
Auch hier gelten die dort aufgeführten Hinweise:
- Jedes DBMS bietet eigene Funktionen sowie Varianten.
- Auf Klammern verzichte ich bei vielen Beschreibungen.
- Die Beispiele werden durch eine verkürzte Schreibweise dargestellt, wobei der Zusatz "from rdb$database" als optional gekennzeichnet ist und nur für Firebird und Interbase gilt.
SELECT 2 * 3 [from rdb%database];
[Bearbeiten] Funktionen für Zahlen
Auch bei diesen weiteren Funktionen müssen Sie auf den Typ achten.
[Bearbeiten] POWER, SQRT – Potenzen und Wurzeln
Mit POWER wird eine beliebige Potenz oder Wurzel berechnet:
POWER( <basis>, <exponent> )
Sowohl für <basis> als auch für <exponent> sind nicht nur ganze positive Zahlen, sondern alle Zahlen zulässig. Mit Dezimalzahlen als <exponent> werden (genau nach mathematischen Regeln) beliebige Wurzeln berechnet; in diesem Fall sind als <basis> negative Zahlen unzulässig. Beispiele:
SELECT POWER( 5, 3 ) [from rdb$database]; /* Ergebnis: 125,000 */ SELECT POWER( 5, 2.5) [from rdb$database]; /* Ergebnis: 55,902 */ SELECT POWER( 5, 0.5) [from rdb$database]; /* Ergebnis: 2,236 das ist Wurzel aus 5 */ SELECT POWER( 0.5, -3 ) [from rdb$database]; /* Ergebnis: 8,000 das ist 3.Potenz aus 2 */ SELECT POWER( 12.35, 1.5) [from rdb$database]; /* Ergebnis: 43,401 */ SELECT POWER(-12.35, 1.5) [from rdb$database]; /* expression evaluation not supported. */ SELECT POWER( 12.35,-1.5) [from rdb$database]; /* Ergebnis: 0,023 */ SELECT POWER(-12.35,-1.5) [from rdb$database]; /* expression evaluation not supported */
Mit SQRT (= Square Root) gibt es für die Quadratwurzel eine kürzere Schreibweise anstelle von POWER(x,0.5):
SELECT SQRT(12.25) [from rdb$database]; /* Ergebnis: 3,500 */
[Bearbeiten] EXP, LOG – Exponentialfunktion und Logarithmen
Mit EXP wird die 
Exponentialfunktion im engeren Sinne bezeichnet, also mit der Eulerschen Zahl e als Basis.
SELECT EXP(1) [from rdb%database]; /* Ergebnis: 2,71828182845905 */
Mit LOG(<wert>, <basis>) wird umgekehrt ein Logarithmus bestimmt, mit LN der natürliche und mit LOG10 der dekadische Logarithmus.
SELECT LOG(10, EXP(1)) [from rdb%database]; /* Ergebnis: 2,30258509299405 */ SELECT LOG(10, 10) [from rdb%database]; /* Ergebnis: 1,000 */ SELECT LN(10) [from rdb%database]; /* Ergebnis: 2,30258509299405 */
[Bearbeiten] Winkelfunktionen
Die trigonometrischen Funktionen arbeiten mit dem Bogenmaß.
SIN Sinus COS Cosinus TAN Tangens COT Cotangens ASIN Arcussinus als Umkehrfunktion des Sinus ACOS Arcuscosinus als Umkehrfunktion des Cosinus ATAN Arcustangens als Umkehrfunktion des Tangens
Mit DEGREES wird ein Bogenmaß in Grad umgerechnet, mit RADIANS ein Gradmaß in das Bogenmaß.
PI liefert die entsprechende Zahl und kann auch für die trigonometrischen Funktionen verwendet werden:
SELECT SIN( PI()/6 ) [from rdb$database]; /* π/6 sind 30°, also Ergebnis 0,5 */
[Bearbeiten] ABS, RAND, SIGN – verschiedene Funktionen
Mit ABS wird der absolute Betrag der gegebenen Zahl zurückgegeben.
SIGN liefert als Hinweis auf das Vorzeichen der gegebenen Zahl einen der Werte 1, 0, -1 – je nachdem, ob die gegebene Zahl positiv, 0 oder negativ ist.
SELECT SIGN(12.34), SIGN(0), SIGN(-5.67) from rdb$database; /* Ergebnis: 1 0 -1 */
RAND liefert eine Zufallszahl im Bereich zwischen 0 und 1 (jeweils einschließlich). Bitte beachten Sie, dass dies keine echten Zufallszahlen sind, sondern Pseudozufallszahlen – siehe Zufallszahl.
Mit RAND(<vorgabewert>) wird innerhalb einer Sitzung immer dieselbe Zufallszahl erzeugt.
Mit einer Kombination von RAND und FLOOR erhält man eine "zufällige" Folge ganzer Zahlen:
FLOOR( <startwert> + ( RAND() * <zielwert> - <startwert> + 1 ) )
Beispielsweise liefert die mehrfache Wiederholung der folgenden Abfrage diese Zahlen zwischen 7 und 12:
SELECT FLOOR(7 + (RAND() * 6)) [from rdb%database]; /* Ergebnisse: 10 9 9 7 8 7 9 9 10 12 usw. */
Diese Funktion ist geeignet, um Datensätze mit SELECT in beliebiger Reihenfolge abzurufen:
SELECT * FROM <tabelle> ORDER BY RAND();
[Bearbeiten] Funktionen für Zeichenketten
Auch zur Bearbeitung und Prüfung von Zeichenketten (Strings) gibt es weitere Funktionen.
[Bearbeiten] Verknüpfen von Strings
Zu den Standardverfahren || + CONCAT gibt es Ergänzungen.
MySql bietet mit CONCAT_WS eine nützliche Erweiterung, bei der zwischen den Teiltexten ein Trennzeichen gesetzt wird.
SPACE(n) – für MS-SQL und MySql – erzeugt einen String, der aus n Leerzeichen besteht.
REPEAT( <text>, <n> ) – für My-SQL – und REPLICATE( <text>, <n> ) – für MS-SQL – erzeugen eine neue Zeichenkette, in der der <text> n-mal wiederholt wird.
Mit LPAD wird <text1>, sofern erforderlich, auf die gewünschte <länge> gebracht und dabei von links mit <text2> bzw. Leerzeichen aufgefüllt. Mit RPAD wird er von rechts aufgefüllt. MS-SQL kennt diese Funktionen nur für Access.
LPAD ( <text1>, <länge> [ , <text2> ] ) RPAD ( <text1>, <länge> [ , <text2> ] )
Wenn der dadurch erzeugte Text zu lang wird, wird zuerst <text2> und notfalls auch <text1> abgeschnitten. Beispiele:
SELECT LPAD( CAST(12345 AS CHAR(8)), 10, '0') [from rdb$database]; /* Ergebnis: '0012345 ' */
Nanu, das sind doch nur 7 Ziffern? Achso, zuerst wird mit CAST ein 8 Zeichen langer String erzeugt; dann ist nur noch Platz für 2 Nullen. Also muss es mit einer dieser Varianten gehen:
SELECT LPAD( CAST(12345 AS CHAR(5)), 10, '0') [from rdb$database]; /* Ergebnis: '0000012345' */ SELECT LPAD( 12345, 10, '0' ) [from rdb$database]; /* Ergebnis: '0000012345' */ SELECT LPAD( 'Hilfe', 10, '-_/') [from rdb$database]; /* Ergebnis: '-_/-_Hilfe' */ SELECT LPAD( 'Ich brauche Hilfe', 10, '-_/') [from rdb$database]; /* Ergebnis: 'Ich brauch' */ SELECT RPAD( 'Hilfe', 10, '-_/') [from rdb$database]; /* Ergebnis: 'Hilfe-_/-_' */ SELECT RPAD( 'Ich brauche Hilfe', 10, '-_/') [from rdb$database]; /* Ergebnis: 'Ich brauch' */
[Bearbeiten] LEFT, RIGHT – Teile von Zeichenketten
Als Ergänzung zu SUBSTRING wird mit LEFT( <text>, <anzahl> ) der linke Teil, also der Anfang eines Textes mit der gewünschten Länge <anzahl> ausgegeben. Ebenso erhält man mit RIGHT( <text>, <anzahl> ) den rechten Teil, also das Ende eines Textes.
SELECT LEFT ('Abc Def Ghi', 5) [from rdb$database]; /* Ergebnis: 'Abc D' */
SELECT RIGHT('Abc Def Ghi', 5) [from rdb$database]; /* Ergebnis: 'f Ghi' */
[Bearbeiten] TRIM, LTRIM, RTRIM – Leerzeichen u.a. entfernen
Mit der TRIM-Funktion werden bestimmte Zeichen – meistens Leerzeichen – am Anfang und/oder am Ende eines Textes entfernt:
TRIM( [ [ LEADING | TRAILING | BOTH ] [ <zeichen> ] FROM ] <text> )
Die Parameter werden wie folgt benutzt:
- Es soll <zeichen> entfernt werden. Es kann sich um ein einzelnes Zeichen, aber auch um einen Text handeln.
- Wenn dieser Parameter fehlt, wird nach führenden bzw. abschließenden Leerzeichen gesucht.
- Wenn LEADING angegeben ist, werden nur führende Zeichen geprüft, bei TRAILING nachfolgende und bei BOTH sowohl als auch. Wenn nichts davon angegeben wird, wird BOTH angenommen.
Beispiele:
SELECT TRIM( ' Dies ist ein Text. ' ) [from rdb$database]; /* Ergebnis: 'Dies ist ein Text.' */ SELECT TRIM( LEADING 'a' FROM 'abcde' ) [from rdb$database]; /* Ergebnis: 'bcde' */ SELECT TRIM( TRAILING 'e' FROM 'abcde' ) [from rdb$database]; /* Ergebnis: 'abcd' */ SELECT TRIM( 'Test' FROM 'Test als Test') [from rdb$database]; /* Ergebnis: ' als ' */
LTRIM (= Left-Trim) und RTRIM (= Right-Trim) sind Kurzfassungen, bei denen Leerzeichen am Anfang bzw. am Ende entfernt werden; MS-SQL kennt nur diese beiden Kurzfassungen.
[Bearbeiten] Suchen und Ersetzen
Mit POSITION wird der Anfang eines Textes innerhalb eines anderen gesucht.
POSITION( <text1> IN <text2> ) SQL-Standard POSITION( <text1>, <text2> [, <start>] ) nicht bei MySql LOCATE ( <text1>, <text2> [, <start>] ) bei MySql
Bei MS-SQL gibt es diese Funktionen nicht; stattdessen kann (mit abweichender Bedeutung) PATINDEX verwendet werden. Oracle bietet zusätzlich INSTR an.
Die Bedeutung der Parameter dürfte offensichtlich sein:
- <text2> ist der Text, in dem gesucht werden soll.
- <text1> ist ein Teiltext, der in <text2> gesucht wird.
- Sofern <start> angegeben ist, wird erst ab dieser Position innerhalb <text2> gesucht. Wenn <start> fehlt, wird ab Position 1 gesucht.
- Die Funktion gibt die Startposition von <text1> innerhalb von <text2> an. Wenn <text1> nicht gefunden wird, lautet das Ergebnis 0.
Beispiele:
SELECT POSITION( 'ch', 'Ich suche Text' ) [from rdb$database]; /* Ergebnis: 2 */
SELECT POSITION( 'ch', 'Ich suche Text', 3 ) [from rdb$database]; /* Ergebnis: 7 */
SELECT POSITION('sch', 'Ich suche Text' ) [from rdb$database]; /* Ergebnis: 0 */
REPLACE dient zum Ersetzen eines Teiltextes durch einen anderen innerhalb eines Gesamttextes:
REPLACE( <quelltext>, <suche>, <ersetze> )
Die verschiedenen SQL-Dialekte verhalten sich unterschiedlich, ob NULL-Werte oder leere Zeichenketten zulässig sind.
SELECT REPLACE('Ich suche Text', 'ch', 'sch') [from rdb$database]; /* Ergebnis: 'Isch susche Text' */
SELECT REPLACE('Die liebe Seele', 'e', ’’) [from rdb$database]; /* Ergebnis: 'Di lib Sl' */
REVERSE passt zwar nicht zu dem, was man in diesem Zusammenhang erwartet; aber auch diese Funktion ändert einen vorhandenen String, und zwar dadurch, dass die Reihenfolge aller Zeichen umgekehrt wird:
SELECT REVERSE( 'Hilfe' ) [from rdb$database]; /* Ergebnis: 'efliH' */
[Bearbeiten] Funktionen für Datums- und Zeitwerte
Bitte beachten Sie die Besonderheiten der Datentypen je nach DBMS.
[Bearbeiten] Differenzen bei Datum oder Uhrzeit
Dafür gibt es vorzugsweise die DATEDIFF-Funktion in unterschiedlicher Version:
DATEDIFF ( <part>, <start>, <end> ) /* bei MS-SQL oder Firebird */ DATEDIFF ( <start>, <end> ) /* bei MySQL */
Als <part> gibt es die gleichen Varianten wie im vorigen Abschnitt. Das Ergebnis ist vom gleichen Typ.
Beim Vergleich von Start- und Enddatum gilt:
- Das Ergebnis ist positiv, wenn der zweite Wert größer ist als der erste.
- Das Ergebnis ist 0, wenn beide Werte gleich sind.
- Das Ergebnis ist negativ, wenn der zweite Wert kleiner ist als der erste.
Bestimme die Anzahl der Tage seit dem letzten gemeldeten Schadensfall.
SELECT DATEDIFF(DAY, MAX(Datum), CURRENT_DATE) FROM Schadensfall; /* Ergebnis: 49 */
Bestimme die Anzahl der Minuten seit Tagesbeginn.
SELECT DATEDIFF(MINUTE, CAST('00:00' AS TIME), CURRENT_TIME) [FROM rdb$database]; /* Ergebnis: 967 */
Datumsangaben können grundsätzlich auch per Subtraktion verglichen werden, weil "intern" häufig ein Tag gleich 1 ist. Darauf kann man sich aber nicht immer verlassen; und es ist schwierig, die Bruchteile eines Tages zu berücksichtigen. Beispiel:
SELECT (CURRENT_DATE - MAX(Datum)) FROM Schadensfall; /* Ergebnis: 49 */
[Bearbeiten] Werte für Datum oder Uhrzeit ändern
Sehr häufig muss aus einem vorhandenen Datum oder Uhrzeit ein neuer Wert berechnet werden. Der SQL-Standard sieht dazu die direkte Addition und Subtraktion vor:
<datetime> + <value> <datetime> - <value> <value> + <datetime>
<datetime> steht für das gegebene, <value> für den Zeitraum, der addiert oder subtrahiert werden soll.
Aus dem aktuellen Zeitwert '19.09.2009 16:10' wird ein neuer Wert bestimmt, der einen halben Tag in der Zukunft liegt:
SELECT CURRENT_TIMESTAMP + 0.5 [from rdb$database]; /* Ergebnis: '20.09.2009 04:10:39' */
MySql akzeptiert nur ganze Zahlen; deshalb ist explizit die Art des Intervalls anzugeben (siehe Dokumentation).
Da das Umrechnen von Zahlen in Datums- und Zeitwerte und umgekehrt für den Anwender umständlich ist, werden viele zusätzliche Funktionen bereitgestellt. Sehr verbreitet ist DATEADD:
DATEADD( <part>, <value>, <datetime> ) /* Firebird, MS-SQL */ DATE_ADD( <datetime> , INTERVAL <value> <part> ) /* MySql */
Welche Versicherungsverträge laufen schon mehr als 10 Jahre?
SELECT ID, Vertragsnummer, Abschlussdatum FROM Versicherungsvertrag WHERE DATEADD(YEAR, 10, Abschlussdatum) <= CURRENT_DATE; /* Ergebnis: 18 Datensätze */
Als Ergänzung oder Alternative gibt es weitere Funktionen, beispielsweise DATE_SUB als Subtraktion, ADDDATE oder ADD_MONTHS.
[Bearbeiten] Funktionen für logische und NULL-Werte
Neben den Standardprüfungen vor allem bei der WHERE-Klausel und den Operatoren AND, OR, NOT gibt es weitere Prüfungen.
[Bearbeiten] COALESCE – Suche Wert ungleich NULL
Die COALESCE-Funktion sucht in einer Liste von Werten (bzw. Ausdrücken) den ersten, der nicht NULL ist. Wenn alle Werte NULL sind, ist der Rückgabewert (zwangsläufig) NULL.
Nenne zu jedem Mitarbeiter eine Kontaktmöglichkeit: vorzugsweise Mobilnummer, dann Telefonnummer, dann Email-Adresse.
SELECT Name, Vorname, COALESCE(Mobil, Telefon, Email) AS Kontakt FROM Mitarbeiter;
Das Ergebnis überrascht zunächst, denn einige Mitarbeiter hätten danach keine Kontaktmöglichkeit. Bei der Abfrage nach IS NULL zur WHERE-Klausel wird aber erläutert, dass eine leere Zeichenkette ungleich NULL ist; bei diesen Mitarbeitern wird also ein leerer Eintrag, aber nicht "nichts" angezeigt.
- Bitte nehmen Sie diesen Hinweis als Empfehlung, lieber NULL zu speichern als den leeren String ’’.
[Bearbeiten] NULLIF
Die Funktion NULLIF vergleicht zwei Werte und liefert NULL zurück, wenn beide Werte gleich sind; andernfalls liefert der erste Wert das Ergebnis.
Suche alle Versicherungsnehmer, die im Alter von 18 Jahren ihren Führerschein gemacht haben.
SQL-Quelltext:
SELECT Name, Vorname, EXTRACT(YEAR FROM Geburtsdatum) + 18 AS Jahr FROM Versicherungsnehmer WHERE Geburtsdatum IS NOT NULL AND NULLIF( EXTRACT(YEAR FROM Geburtsdatum) + 18, EXTRACT(YEAR FROM Fuehrerschein) ) IS NULL;
SQL-Ausgabe:
NAME VORNAME JAHR Liebermann Maria 1988
Hinweis: Sowohl COALESCE als auch NULLIF sind Kurzfassungen für spezielle Fallunterscheidungen mit CASE WHEN, in denen zusätzlich IS NULL eingebunden wird – siehe dazu Nützliche Erweiterungen.
[Bearbeiten] Verschiedene Funktionen
Auch wenn die Funktionen in diesem Abschnitt beim SQL-Standard vorgesehen sind, sind sie nicht immer vorhanden. Ich verzichte deshalb wiederum auf nähere Erläuterungen und verweise auf die jeweilige Dokumentation.
[Bearbeiten] ROW_NUMBER – Zeilen nummerieren
Mit der ROW_NUMBER-Funktion werden die Zeilen im Ergebnis einer Abfrage nach der betreffenden Sortierung durchnummeriert. MS-SQL verwendet die folgende Syntax:
ROW_NUMBER() OVER ( [ <partition_by_clause> ] <order_by_clause> )
[Bearbeiten] CURRENT_USER – der aktuelle Benutzer
Mit CURRENT_USER (in der Regel ohne Klammer) wird der aktuelle Benutzername abgefragt. Dieser kann auch per DEFAULT bei Neuaufnahmen automatisch in einer Spalte einer Tabelle eingetragen werden.
SELECT CURRENT_USER [from rdb$database]; /* Ergebnis: SYSDBA */
[Bearbeiten] Zusammenfassung
In diesem Kapitel lernten Sie weitere eingebaute Funktionen kennen:
- Für Zahlen gibt es viele mathematische Funktionen wie Potenzen, Wurzeln, Exponential- oder Winkelfunktionen.
- Zeichenketten können auf vielfache Weise verknüpft oder zum Erstellen neuer Strings bearbeitet werden.
- Datums- und Zeitwerte können im Detail verglichen und verrechnet werden.
