Skalare Funktionen 

Skalare Funktionen lassen sich in die in Tabelle 4.2 aufgelisteten Kategorien unterteilen.

 
Kategorien skalarer Funktionen
 
FunktionskategorieErläuterung
IntegriertFührt Operationen mit in der Datenbank integrierten Werten oder Einstellungen durch.Oracle verwendet den Begriff "integriert" für alle Oracle-spezifischen Funktionen, die in dessen DBMS "eingebaut" sind. Deren Verwendung unterscheidet sich von den hier beschriebenen Funktionen.
Datum und UhrzeitFührt Operationen mit DATETIME-Feldern durch und gibt Werte im DATETIME-Format zurück.
NumerischFührt Operationen mit numerischen Werten durch und gibt numerische Werte zurück.
StringFührt Operationen mit Zeichenwerten (char, varchar, nchar, nvarchar und CLOB) durch und gibt einen String oder einen numerischen Wert zurück.
 
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
 
FunktionVerwendung
CURRENT_DATEGibt das aktuelle Datum zurück.
CURRENT_TIMEGibt die aktuelle Uhrzeit zurück.
CURRENT_TIMESTAMPGibt das aktuelle Datum und die aktuelle Uhrzeit zurück.
CURRENT_USERGibt den gerade aktiven Benutzer im Datenbankserver zurück.
SESSION_USER Gibt die gerade aktive Autorisierungs-ID zurück, wenn sich diese vom Benutzer unterscheidet.
SYSTEM_USER Gibt den gerade aktiven Benutzer auf Betriebssystemebene zurück.
 
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
 
FunktionVerwendung
BIT_LENGTH(expression)Gibt einen Integerwert zurück, der für die Anzahl der Bits in einem Ausdruck steht.
CHAR_LENGTH(expression)Gibt einen Integerwert zurück, der für die Anzahl der Zeichen in einem Ausdruck steht.
EXTRACT(datetime_expression datepart FROM expression)Ermöglicht das Extrahieren des Datumsabschnitts (YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR oder TIMEZONE_MINUTE) aus einem Ausdruck.
OCTET_LENGTH(expression)Gibt einen Integerwert zurück, der für die Anzahl der Oktette in einem Ausdruck steht. Dieser Wert ist identisch mit BIT_LENGTH/8.
POSITION(starting_string IN search_string)Gibt einen Integerwert zurück, der für die Startposition eines Strings in einem Suchstring steht.
 
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
 

Typwert

Bedeutung

Erwartetes Format

SECOND

Sekunden

SECONDS

MINUTE

Minuten

MINUTES

HOUR

Stunden

HOURS

DAY

Tage

DAYS

MONTH

Monate

MONTHS

YEAR

Jahre

YEARS

MINUTE_SECOND

Minuten und Sekunden

"MINUTES:SECONDS"

HOUR_MINUTE

Stunden und Minuten

"HOURS:MINUTES"

DAY_HOUR

Tage und Stunden

"DAYS HOURS"

YEAR_MONTH

Jahre und Monate

"YEARS-MONTHS"

HOUR_SECOND

Stunden, Minuten, Sekunden

"HOURS:MINUTES:SECONDS"

DAY_MINUTE

Tage, Stunden, Minuten

"DAYS HOURS:MINUTES"

DAY_SECOND

Tage, Stunden, Minuten, Sekunden

"DAYSHOURS:MINUTES:SECONDS"

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.

 
 

Funktion

Verwendung

CONCATENATE(expression || expression)

Verknüpft zwei oder mehr Literalausdrücke, Spaltenwerte oder Variablen zu einem String.

CONVERT

Konvertiert einen String in eine andere Darstellungsweise im gleichen Zeichensatz.

LOWER

Konvertiert einen String in Kleinbuchstaben.

SUBSTRING

Extrahiert einen Teil eines Strings.

TRANSLATE

Konvertiert einen String von einem Zeichensatz in einen anderen.

TRIM

Entfernt führende und/oder nachgestellte Zeichen aus einem String.

UPPER

Konvertiert einen String in Großbuchstaben.

 
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 '