Skalare Funktionen | |||||||||||
Skalare Funktionen lassen sich in die in Tabelle 4.2 aufgelisteten Kategorien unterteilen. |
|||||||||||
Kategorien skalarer Funktionen | |||||||||||
|
|||||||||||
Beachten Sie, dass sowohl CASE als auch CAST Funktionen sind. Sie werden jedoch aufgrund ihrer Komplexität und ihrer häufigen Verwendung in SQL-Datenanweisungen in Kapitel 3 behandelt. |
Integrierte skalare Funktionen | |||||||||||||||
Die integrierten skalaren Funktionen in SQL99 geben die aktuelle Benutzersitzung und deren Eigenschaften wie beispielsweise die Privilegien an. Integrierte skalare Funktionen sind fast immer nicht-deterministisch. Die ersten drei in Tabelle 4.3 genannten Funktionen gehören zur Kategorie der Datums- und Uhrzeit-Funktionen. Obwohl die vier Hersteller viele zusätzlichen Funktionen anbieten, die über die hier genannten integrierten SQL-Funktionen hinausgehen, deklariert der SQL-Standard nur die in Tabelle 4.3 aufgeführten Funktionen. |
|||||||||||||||
Integrierte skalare Funktionen in SQL99 | |||||||||||||||
|
|||||||||||||||
Die integrierten skalaren Funktionen in SQL99 geben die aktuelle Benutzersitzung und deren Eigenschaften wie beispielsweise die Privilegien an. Integrierte skalare Funktionen sind fast immer nicht-deterministisch. Die ersten drei in Tabelle 4.3 genannten Funktionen gehören zur Kategorie der Datums- und Uhrzeit-Funktionen. Obwohl die vier Hersteller viele zusätzlichen Funktionen anbieten, die über die hier genannten integrierten SQL-Funktionen hinausgehen, deklariert der SQL-Standard nur die in Tabelle 4.3 aufgeführten Funktionen. | |||||||||||||||
Beispiel | |||||||||||||||
Die folgenden Abfragen geben die Werte von integrierten Funktionen zurück. Beachten Sie, dass die einzelnen Hersteller Datumsangaben jeweils in den nativen Formaten zurückgeben: |
|||||||||||||||
/* On MySQL */ SELECT CURRENT_TIMESTAMP; -> '2001-12-15 23:50:26' /* On Microsoft SQL Server */ SELECT CURRENT_TIMESTAMP GO -> 'Dec 15,2001 23:50:26' /* On Oracle */ SELECT USER FROM dual; -> dylan |
Numerische skalare Funktionen | ||||||||||||||||||||||||||||||||||||||||||
Die Liste der offiziellen numerischen Funktionen in SQL99 ist ziemlich kurz. Die verschiedenen Hersteller bieten aber eine Vielzahl zusätzlicher mathematischer und statistischer Funktionen. MySQL unterstützt viele dieser Befehle in seinen SQL99-Inkarnationen. Die anderen Datenbankprodukte unterstützen dieselben numerischen skalaren Funktionen über ihre eigenen intern definierten Funktionen, verwenden aber andere Namen als der SQL-Standard. Die unterstützten numerischen Funktonen und deren Syntax finden Sie in Tabelle 4.4. |
||||||||||||||||||||||||||||||||||||||||||
Numerische Funktionen in SQL99 | ||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||
BIT_LENGTH, CHAR_LENGTH, and OCTET_LENGTH | ||||||||||||||||||||||||||||||||||||||||||
Von allen Herstellern kommt Oracle der Funktion BIT_LENGTH am nächsten. Oracle unterstützt die Funktion LENGTHB, die einen Integerwert zurückgibt, der für die Anzahl der Byte in einem Ausdruck steht. MySQL und PostgreSQL unterstützen CHAR_LENGTH und das SQL99-Synonym CHARACTER_LENGTH( ). PostgreSQL unterstützt außerdem EXTRACT( ), OCTET_LENGTH( ) und POSITION( ) entsprechend dem SQL99-Standard. Die beiden anderen Hersteller haben vergleichbare Funktionen mit identischer Funktionalität, bei SQL Server ist dies die Funktion LEN, bei Oracle die Funktion LENGTH. MySQL und PostgreSQL unterstützen darüber hinaus auch vollständig die Funktion OCTET_LENGTH. |
||||||||||||||||||||||||||||||||||||||||||
Beispiel | ||||||||||||||||||||||||||||||||||||||||||
Im folgenden Beispiel wird die Länge eines Strings und eines Wertes festgelegt, die aus einer Spalte abgerufen wurden: |
||||||||||||||||||||||||||||||||||||||||||
/* On MySQL and PostgreSQL */ SELECT CHAR_LENGTH('hello'); SELECT OCTET_LENGTH(book_title) FROM titles; /* On Microsoft SQL Server */ SELECT DATALENGTH(title) FROM titles WHERE type = 'popular_comp' GO /* On Oracle */ SELECT LENGTH('HORATIO') "Length of characters" FROM dual; |
||||||||||||||||||||||||||||||||||||||||||
EXTRACT | ||||||||||||||||||||||||||||||||||||||||||
Die Funktion EXTRACT wird nur von PostgreSQL und MySQL unterstützt. Jeder Hersteller unterstützt aber einen anderen Befehl, der dieselbe Funktionalität bietet. Oracle verwendet die Funktion TO_CHAR, um einen Teil eines Datumswerts in einen Zeichenstring zu extrahieren. In SQL Server geschieht das mit der Funktion CONVERT. Die MySQL-Implementierung geht etwas über den SQL99-Standard hinaus. In diesem ist die Rückgabe mehrerer Felder in einem Aufruf von EXTRACT( ) (z. B. "DAY_HOUR") nicht vorgesehen. Mit den MySQL-Erweiterungen soll das erreicht werden, was die Kombination DATE_TRUNC( ) und DATE_PART( ) in PostgreSQL bewirkt. MySQL unterstützt die in Tabelle 4.5 aufgeführten Datumsbestandteile. |
||||||||||||||||||||||||||||||||||||||||||
Datumsbestandteile in MySQL | ||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||
Beispiel | ||||||||||||||||||||||||||||||||||||||||||
Im folgenden Beispiel werden Datumsbestandteile aus verschiedenen Datums- und Uhrzeitwerten extrahiert: |
||||||||||||||||||||||||||||||||||||||||||
/* On MySQL */ SELECT EXTRACT(YEAR FROM "2013-07-02"); -> 1999 SELECT EXTRACT(YEAR_MONTH FROM "2013-07-02 01:02:03"); -> 199907 SELECT EXTRACT(DAY_MINUTE FROM "2013-07-02 01:02:03"); -> 20102 |
||||||||||||||||||||||||||||||||||||||||||
POSITION | ||||||||||||||||||||||||||||||||||||||||||
Die Funktion POSITION gibt einen Integerwert zurück, der die Startposition eines Strings in einem Suchstring angibt. MySQL und PostgreSQL unterstützen die Funktion POSITION ohne Abweichung von der SQL99-Syntax. PostgreSQL verfügt über eine synonyme Funktion namens TEXTPOS, MySQL über die synonyme Funktion LOCATE. In Oracle lautet das Äquivalent INSTR. Microsoft SQL Server hat sowohl CHARINDEX als auch PATINDEX. CHARINDEX und PATINDEX sind sich sehr ähnlich, außer dass PATINDEX die Verwendung von Wildcard-Zeichen in den Suchkriterien erlaubt. Beispiel: /* On MySQL */ SELECT LOCATE('bar', 'foobar'); -> 4 /* On MySQL and PostgreSQL */ SELECT POSITION('fu' IN 'snafhu'); -> 0 /* On Microsoft SQL Server */ SELECT CHARINDEX( 'de', 'abcdefg' ) GO -> 4 SELECT PATINDEX( '%fg', 'abcdefg' ) GO -> 6 |
Stringfunktionen | |||||||||||||||||
Grundlegende Stringfunktionen bieten eine Vielzahl von Möglichkeiten und geben immer einen Stringwert als Ergebnismenge zurück. Manche Stringfunktionen sind dyadisch, d. h. sie verarbeiten zwei Strings gleichzeitig. SQL99 unterstützt die in Tabelle 4.6 genannten Stringfunktionen. |
|||||||||||||||||
|
|||||||||||||||||
CONCATENATE | |||||||||||||||||
SQL99 definiert einen Verkettungsoperator ( || ), der zwei Strings zu einem Stringwert zusammenfasst. Mit der Funktion CONCATENATE werden zwei oder mehr Strings zu einem einzigen Ausgabestring zusammengefasst. PostgreSQL und Oracle unterstützen den Verkettungsoperator mit den zwei senkrechten Strichen. Microsoft SQL Server dagegen verwendet das Pluszeichen (+) als Verkettungsoperator. MySQL unterstützt eine ähnliche Funktion, nämlich CONCAT( ). Nähere Informationen zur Verkettung in Oracle, PostgreSQL und Microsoft SQL Server finden Sie im Abschnitt Verkettungsoperatorenin Kapitel 3. |
|||||||||||||||||
Syntax in SQL99 | |||||||||||||||||
CONCATENATE('string1' || 'string2') |
|||||||||||||||||
Syntax in MySQL | |||||||||||||||||
CONCAT(str1, str2, [,...n]) |
|||||||||||||||||
Wenn einer der Verkettungswerte null ist, ist der gesamte zurückgegebene String null. Wenn ein numerischer Wert verknüpft wird, wird er implizit in einen String konvertiert: |
|||||||||||||||||
SELECT CONCAT('My ', 'bologna ', 'has ', 'a ', 'first ', 'name...'); -> 'My bologna has a first name...' SELECT CONCAT('My ', NULL, 'has ', 'first ', 'name...'); -> NULL |
|||||||||||||||||
CONVERT und TRANSLATE | |||||||||||||||||
Mit der Funktion CONVERT wird die Darstellung eines Zeichenstrings innerhalb eines Zeichensatzes und einer Sortierreihenfolge verändert. CONVERT kann beispielsweise dazu verwendet werden, die Anzahl der Bits pro Zeichen zu ändern. TRANSLATE hingegen ändert den Basiszeichensatz eines Stringwerts. So kann mit TRANSLATE zum Beispiel ein Wert aus einem westeuropäischen Zeichensatz in einen japanischen Kanji-Zeichensatz oder einen russischen Zeichensatz geändert werden. Die Übersetzung muss bereits vorhanden sein, entweder per Voreinstellung oder mit dem Befehl CREATE TRANSLATION erzeugt. |
|||||||||||||||||
Syntax in SQL99 | |||||||||||||||||
CONVERT (char_value target_char_set USING form_of_use source_char_name) TRANSLATE(char_value target_char_set USING translation_name) |
|||||||||||||||||
Von den hier besprochenen Datenbankherstellern unterstützt nur Oracle CONVERT und TRANSLATE mit derselben Bedeutung wie in SQL99. Die Oracle-Implementierung von TRANSLATE ist der von SQL99 sehr ähnlich, die beiden sind jedoch nicht identisch. In der eigenen Implementierung akzeptiert Oracle nur zwei Argumente und erlaubt nur die Übersetzung zwischen dem Zeichensatz der Datenbank und dem NLS-Zeichensatz. |
|||||||||||||||||
Die MySQL-Implementierung der Funktion CONVERT übersetzt nur Zahlen von einer Basis in eine andere. Die CONVERT-Implementierung von Microsoft SQL Server hingegen ist eine sehr umfangreiche Hilfsfunktion, die den Basisdatentyp eines Ausdrucks ändert, ansonsten aber nicht viel mit der CONVERT-Funktion in SQL99 gemeinsam hat. PostgreSQL unterstützt CONVERT nicht; die PostgreSQL-Implementierung von TRANSLATE dient dazu, eine Instanz eines Zeichenstrings in einen anderen Zeichenstring umzuwandeln. |
|||||||||||||||||
MySQL: Syntax und Variationen | |||||||||||||||||
CONV(int, from_base, to_base) |
|||||||||||||||||
MySQL unterstützt TRANSLATE nicht. Die MySQL-Implementierung von CONVERT gibt einen Stringwert zurück, der für die Zahl steht, die von "from_base value" in "to_base value" konvertiert wurde. Wenn eine der Zahlen NULL ist, gibt die Funktion NULL zurück. Nachfolgend einige Beispiele: |
|||||||||||||||||
SELECT CONV("a",16,2); -> '1010' SELECT CONV("6E",18,8); -> '172' SELECT CONV(-17,10,-18); -> '-H' |
|||||||||||||||||
Microsoft SQL Server: Syntax und Variationen | |||||||||||||||||
CONVERT (data_type[(length) | (precision,scale)], expression[,style]) |
|||||||||||||||||
Microsoft SQL Server unterstützt TRANSLATE nicht. Die Microsoft-Implementierung der Funktion CONVERT folgt nicht der SQL99-Spezifikation. Sie entspricht stattdessen in ihrer Funktionsweise der Funktion CAST . Die Stilklausel wird verwendet, um das Format einer Datumskonvertierung anzugeben. Näheres dazu finden Sie in der Dokumentation des Herstellers. Beispiel: |
|||||||||||||||||
SELECT title, CONVERT(char(7), ytd_sales) FROM titles ORDER BY title GO |
|||||||||||||||||
Oracle: Syntax und Variationen | |||||||||||||||||
CONVERT('char_value', target_char_set, source_char_set) TRANSLATE('char_value', 'from_text', 'to_text') |
|||||||||||||||||
In der Oracle-Implementierung gibt die Funktion CONVERT den char_value im Zielzeichensatz zurück. Der char_value ist der zu konvertierende String, target_char_set der Name des Zeichensatzes, in den der char_value konvertiert wird. Source_char_set bezeichnet den Zeichensatz, in dem der char_value ursprünglich gespeichert war. |
|||||||||||||||||
Sowohl der Ziel- als auch der Ausgangszeichensatz können aus Literalstrings, Variablen oder Spalten bestehen, die den Namen des Zeichensatzes enthalten. Beachten Sie, dass unzulängliche Ersetzungszeichen ersetzt werden können, wenn aus oder in einen Zeichensatz konvertiert wird, der nicht alle in der Konvertierung vorkommenden Zeichen unterstützt. |
|||||||||||||||||
Oracle unterstützt mehrere gängige Zeichensätze wie US7ASCII, WE8DECDEC, WE8HP, F7DEC, WE8EBCDIC500, WE8PC850 und WE8ISO8859P1. Beispiel: |
|||||||||||||||||
SELECT CONVERT('Groß', 'US7ASCII', 'WE8HP') FROM DUAL; ->Gross |
|||||||||||||||||
PostgreSQL: Syntax und Variationen | |||||||||||||||||
TRANSLATE (character_string, from_text, to_text) |
|||||||||||||||||
PostgreSQL unterstützt CONVERT nicht. Die PostgreSQL-Implementierung der Funktion TRANSLATE bietet dagegen viel mehr als in der SQL99-Spezifikation angegeben. Sie konvertiert jedes Auftreten eines Textstrings in einen anderen String innerhalb eines weiteren angegebenen Strings. Beispiel: |
|||||||||||||||||
SELECT TRANSLATE('12345abcde', '5a', 'XX'); -> 1234XXbcde SELECT TRANSLATE(title, 'Computer', 'PC') FROM titles WHERE type = 'Personal_computer' |
|||||||||||||||||
LOWER und UPPER | |||||||||||||||||
Mit den Funktionen LOWER und UPPER können Sie einen String schnell und einfach in Klein- bzw. Großbuchstaben konvertieren. Diese Funktionen werden in allen in diesem Buch behandelten Datenbankimplementierungen unterstützt. |
|||||||||||||||||
Beispiel | |||||||||||||||||
SELECT LOWER('You Talkin To ME?'), UPPER('you talking to me?!'); -> you talking to me?, YOU TALKIN TO ME?! |
|||||||||||||||||
Die einzelnen Datenbankhersteller unterstützen auch eine Vielzahl implementierungsspezifischer Textformatierungsfunktionen. |
|||||||||||||||||
SUBSTRING | |||||||||||||||||
Mit der Funktion SUBSTRING können Sie einen Zeichenstring aus einem anderen Zeichenstring extrahieren. |
|||||||||||||||||
Syntax in SQL99 | |||||||||||||||||
SUBSTRING(extraction_string FROM starting_position [FOR length] [COLLATE collation_name]) |
|||||||||||||||||
Wenn einer der Eingabeparameter NULL ist, gibt die Funktion NULL zurück. Dieextraction_string ist der String, aus dem der Zeichenwert extrahiert wird. Es kann sich dabei um einen Literalstring, eine Spalte in einer Tabelle mit einem Zeichendatentyp oder eine Variable mit einem Zeichendatentyp handeln. starting_position ist ein Integerwert, der angibt, ab welcher Position extrahiert werden soll. length ist ein optionaler Integerwert, der angibt, wie viele Zeichen beginnend bei der starting_position extrahiert werden sollen. |
|||||||||||||||||
MySQL: Syntax und Variationen | |||||||||||||||||
SUBSTRING(extraction_string FROM starting_position) |
|||||||||||||||||
Die MySQL-Implementierung geht davon aus, dass die Zeichen von der Startposition bis zum Ende des Zeichenstrings extrahiert werden sollen. |
|||||||||||||||||
Microsoft SQL Server: Syntax und Variationen | |||||||||||||||||
SUBSTRING(extraction_string [FROM starting_position] [FOR length]) |
|||||||||||||||||
Microsoft SQL Server unterstützt weitestgehend den SQL99-Standard, allerdings nicht die COLLATE-Klausel. Bei Microsoft kann dieser Befehl auf Text, Bilder und binäre Datentypen angewendet werden; starting_position und length stehen dabei für die Anzahl der Byte und nicht für die Anzahl der zu zählenden Zeichen. |
|||||||||||||||||
Oracle: Syntax und Variationen | |||||||||||||||||
SUBSTR(extraction_string, starting_position [, length]) |
|||||||||||||||||
Die Oracle-Implementierung, SUBSTR, funktioniert weitestgehend wie in SQL99 beschrieben; die COLLATE-Klausel wird jedoch nicht unterstützt. Wenn starting_value eine negative Zahl ist, zählt Oracle vom Ende des extraction_string an. Wenn length weggelassen wird, wird der Rest des Strings (beginnend bei starting_position) zurückgegeben. |
|||||||||||||||||
PostgreSQL: Syntax und Variationen | |||||||||||||||||
SUBSTRING(extraction_string [FROM starting_position] [FOR length]) |
|||||||||||||||||
Microsoft SQL Server unterstützt weitestgehend den SQL99-Standard, allerdings nicht die COLLATE-Klausel. |
|||||||||||||||||
Beispiele | |||||||||||||||||
Die folgenden Beispiele funktionieren fast alle mit allen vier in diesem Buch behandelten Datenbanken. Nur das zweite, Oracle-spezifische Beispiel mit einer negativen Startposition funktioniert nicht mit den anderen Datenbanken (angenommen, die SUBSTR von Oracle wird in SUBSTRING geändert): |
|||||||||||||||||
/* Oracle, ab der linken Seite */ SELECT SUBSTR('ABCDEFG',3,4) FROM DUAL; -> CDEF /* On Oracle, ab der rechten Seite */ SELECT SUBSTR('ABCDEFG',-5,4) FROM DUAL; -> CDEF /* MySQL */ SELECT SUBSTRING('Be vewy, vewy quiet',5); -> 'wy, vewy quiet'' /* PostgreSQL oder SQL Server */ SELECT au_lname, SUBSTRING(au_fname, 1, 1) FROM authors WHERE au_lname = 'Carson' -> Carson C |
|||||||||||||||||
TRIM | |||||||||||||||||
Die Funktion TRIM entfernt führende Leerzeichen und/oder nachgestellte Zeichen aus dem angegebenen Zeichenstring. Mit dieser Funktion können auch andere Zeichentypen aus dem angegebenen Zeichenstring entfernt werden. Standardmäßig wird das angegebene Zeichen von beiden Seiten des Zeichenstrings entfernt. Wenn kein String zum Entfernen angegeben ist, entfernt TRIM standardmäßig Leerzeichen. |
|||||||||||||||||
Syntax in SQL99 | |||||||||||||||||
TRIM( [ [{LEADING | TRAILING | BOTH}] [removal_string] FROM ] target_string [COLLATE collation_name]) |
|||||||||||||||||
"removal_string" ist der zu entfernende Zeichenstring. "target _string" ist der Zeichenstring, aus dem Zeichen entfernt werden sollen. Wenn kein "removal_string" angegeben ist, entfernt TRIM Leerzeichen. Die COLLATE-Klausel setzt die Ergebnismenge der Funktion in eine andere bereits vorhandene Sortierreihenfolge um. |
|||||||||||||||||
MySQL, PostgreSQL und Oracle unterstützen die SQL99-Syntax von TRIM. |
|||||||||||||||||
Microsoft SQL Server (und auch die anderen Hersteller) stellt die Funktionen LTRIM und RTRIM zur Verfügung, um führende und/oder nachgestellte Leerzeichen zu entfernen. Mit LTRIM und RTRIM können keine anderen Zeichen entfernt werden. |
|||||||||||||||||
Beispiele | |||||||||||||||||
SELECT TRIM(' wamalamadingdong '); -> 'wamalamadingdong' SELECT TRIM(LEADING '19' FROM '1976 AMC GREMLIN'); -> '76 AMC GREMLIN' SELECT TRIM(BOTH 'x' FROM 'xxxWHISKEYxxx'); -> 'WHISKEY' SELECT TRIM(TRAILING 'snack' FROM 'scooby snack'); -> 'scooby ' |