Single Row Functions

Eine Übersicht befindet sich in [14].

Character / String Functions

Häufig ist es notwendig, die Spalten der Ergebnismenge gemäß einer Vorgabe zu gestalten. Hierfür stellt SQL eine Reihe von Funktionen zur Verfügung. Beachten Sie bitte, dass sich diese Funktionen nur auf die Anzeige auswirken, nicht jedoch auf die eigentlichen gespeicherten Daten. Diese werden in keiner Weise verändert.

Zeichenkettenfunktionen können nicht nur zwischen SELECT und FROM benutzt werden, sondern auch im WHERE-Ausdruck. Im folgenden sehen wir uns drei Zeichenkettenfunktionen bezugnehmend auf Groß- und Kleinschreibung an.

LOWER()

Mit der Funktion LOWER wird eine Zeichenkette in Kleinbuchstaben umgewandelt.

select lower(ename) as Nachname from emp;
NACHNAME
--------
smith
allen
ward
...

Diese Abfrage gibt als Ergebnismenge die Nachnamen (ename) der Mitarbeiter in Kleinbuchstaben zurück

UPPER()

Es wird durch die Funktion UPPER eine Zeichenkette in Großbuchstaben umgewandelt.

select upper(ename) as Nachname from emp;
NACHNAME
--------
SMITH
ALLEN
WARD
...

Diese Abfrage gibt als Ergebnismenge die Nachnamen (ename) der Mitarbeiter in Großbuchstaben zurück.

INITCAP()

INITCAP gibt den ersten Buchstaben groß, alle weiteren klein geschrieben zurück.

select initcap(ename) as Nachname from emp;
NACHNAME
--------
Smith
Allen
Ward
...

Diese Abfrage gibt als Ergebnismenge die Nachnamen (ename) der Mitarbeiter zurück. Der erste Buchstabe wird groß geschrieben und alle weiteren werden klein geschrieben.

Zusammenfassung

• LOWER() wandelt die Anzeige der Spalte / des Ausdruckes x in Kleinbuchstaben um.
• UPPER() wandelt die Anzeige der Spalte / des Ausdruckes x in Großbuchstaben um.
• INITCAP() wandelt das erste Zeichen der Anzeige der Spalte / des Ausdruckes x in Großbuchstaben um - alles andere bleibt klein.

Neben den im vorigen Kapitel besprochenen Zeichenkettenfunktionen gibt es noch eine große Anzahl weiterer Funktionen. Hier soll nur ein Auszug der wichtigsten Funktionen dargestellt werden.

CONCAT()

select concat(ename,job) as Nachname from emp;
NACHNAME
--------------
SMITHCLERK
ALLENSALESMAN
WARDSALESMAN
...

Es werden die Spalten Nachname (ename) und Job verknüpft. Dies entspricht dem Verknüpfungsoperator ||, wobei concat() aber nur 2 Argumente verarbeiten kann.

SUBSTR()

select ename as Nachname, substr(ename,1,3) from emp;
NACHNAME   SUB
-----------------
SMITH      SMI
ALLEN      ALL
WARD       WAR
...

Diese Abfrage gibt eine Zeichenkette von 3 Zeichen, beginnend beim ersten Zeichen, zurück.

select ename as Nachname, substr(ename,3,2) from emp;
NACHNAME   SU
-------------
SMITH      IT
ALLEN      LE
WARD       RD
...

Diese Abfrage gibt eine Zeichenkette von 2 Zeichen, beginnend beim dritten Zeichen, zurück.

select ename as Nachname, substr(ename,-3,2) from emp;
NACHNAME   SU
-------------
SMITH      IT
ALLEN      LE
WARD       AR
...

Diese Abfrage gibt eine Zeichenkette von 2 Zeichen, beginnend beim dritten Zeichen von hinten, zurück.

INSTR()

select ename as Nachname, instr(ename,'S') from emp;
NACHNAME INSTR(ENAME,'S')
---------------------------
SMITH       1
ALLEN       0
WARD        0
...

Es wird in der Spalte Nachname (ename) nach dem Vorkommen von 'S' gesucht und die Position zurückgegeben. Wird ,S' nicht gefunden, so wird eine 0 zurückgegeben.

LENGTH()

select ename as Nachname, length(ename) as Länge from emp;
NACHNAME  LÄNGE
----------------
SMITH       5
ALLEN       5
WARD        4
...

Es wird die Länge der Spalte Nachname (ename) zurückgegeben.

SELECT productname, length(productname) AS Laenge FROM products;
PRODUCTNAME  LAENGE
-------------------
Milch            20
Käse             20
...

Es wird die Länge der Daten in der Spalte productname ermittelt. Da der Datentyp der Spalte productname aber CHAR ist, wird der verbleibende Platz bis zur bei CHAR angegebenen Länge mit Leerzeichen aufgefüllt. LENGTH gibt bei CHAR also immer die gleiche Länge aus, nämlich den Wert, der bei CHAR angegeben wurde.

TRIM()

select trim(' ' from '   Tech    ') from dual;

Es werden die vorangestellten und nachfolgenden Leerzeichen entfernt.

SELECT productname,length(trim (' ' from productname) AS Laenge
 FROM products;
PRODUCTNAME  LAENGE
------------------------
Milch             5
Käse              4
...

Es wird die Länge der Daten in der Spalte productname ermittelt. TRIM schneidet diese Leerzeichen ab. Es wird demnach als Ergebnis die echte Länge angegeben.

LTRIM()

select ltrim('    Tech') from dual;

Es werden die vorangestellten Leerzeichen entfernt.

RTRIM()

select rtrim('Tech    ') from dual;

Es werden die angefügten Leerzeichen entfernt.

RPAD()

select rpad('Tech',10,'*') from dual;

Es werden bis zur Gesamtlänge von 10 Zeichen mit * rechts aufgefüllt.

LPAD()

select lpad('Tech',10,'*') from dual;

Es werden bis zur Gesamtlänge von 10 Zeichen mit * links aufgefüllt.

REPLACE()

replace(s1,s2[,s3])

Suche s2 in s1 und ersetze ihn durch s3. Ist s3 nicht angegeben, wird s2 in s1 entfernt.

select replace('SCHADE', 'D', 'LK') from dual;
'SCHALKE'.

TRANSLATE()

translate(s1,s2,s3)

In s1 werden alle Zeichen aus s2 durch solche aus s3 ersetzt.

select translate('ABC67LR5', '0123456789','**********') from dual;
'ABC**LR*'

select translate('ABC67LR5', '*0123456789','*') from dual;
'ABCLR'.

CHR()

select chr(65) from dual;
'A'

ASCII()

select ascii('A') from dual;
65

Conversion Functions

Wichtige Zahlenformate

Format		Beschreibung	Beispiel
999999		5 Stellen	1234
099999		Auffüllen mit Nullen	001234

Häufige Zahlenformate

9		Stellvertreter für eine Ziffer (keine führenden Nullen)
0		Stellvertreter für eine Ziffer (Führende Nullen)
D		Dezimaltrennzeichen
G		Tausenedertrennzeichen
L		Position des Währungssymbols, NLS_CURRENCY

Wichtige Datumsformate

DD		zweistelliger Tageswert
DAY		ausgeschriebener Tag
MM		zweistelliger Monatswert
Month		ausgeschriebener Monat
YY		zweistelliger Jahreswert
YYYY		vierstelliger Jahreswert

Beispiele für Datumsformate:
DD.MM.YYYY - 11.02.2003
Day, "der" DD.MM.YYYY - Dienstag, der 11.02.2003
Day, "der" DD Month YYYY - Dienstag, der 11 Februar 2003

TO_CHAR()

select ename as "Nachname", to_char(sal,'09999') as Gehalt from emp;
Nachname   GEHALT
-----------------
SMITH       00800
ALLEN       01600
...

Es wird das Gehalt der Angestellten in eine Zeichenkette konvertiert und mit 5 Stellen angegeben. Falls das Gehalt nicht 5 Stellen beträgt, so wird es mit führenden Nullen aufgefüllt. Dies ist zum Beispiel bei der PLZ in Deutschland sinnvoll.

select ename as "Nachname", to_char(sal,'09G999D00') as Gehalt from emp;

Es wird das Gehalt der Angestellten in eine Zeichenkette konvertiert und mit 5 Stellen vor dem Komma (mit Tausender-Trennzeichnen) und zwei Stellen nach dem Dezimalzeichen ausgegeben.

select ename as "Nachname", 
       to_char(hiredate,'Day, "dem" dd. Month yyyy') as "Eingestellt am" 
  from emp
;
Nachname  Eingestellt am
--------------------------------------------
SMITH     Mittwoch , dem 17. Dezember  1980
ALLEN     Freitag  , dem 20. Februar   1981
...

Es wird das Einstellungsdatum der Angestellten in eine Zeichenkette konvertiert und im Format 'Day, " dem " dd. Month yyyy' ausgegeben.

TO_NUMBER()

select to_number('   1,5') as Zahl from dual;
ZAHL
-----
 1,5

Die Zeichenkette ' 1,5' wird in eine Zahl konvertiert.

TO_DATE()

select to_date('01.01.2001','dd.mm.yyyy') as Datum from dual;
DATUM
--------
01.01.01

Die Zeichenkette '01.01.2001' wird gemäß des Formates 'dd.mm.yyyy' als Datum interpretiert und in ein Datum konvertiert.

Advanced Functions

NVL()

Mit Hilfe der Funktion NVL ist es möglich, NULL-Werte durch beliebige Werte zu ersetzen. NVL(Comm,0) ersetzt als Beispiel alle NULL-Werte in der Spalte Comm durch eine echte 0. Betont werden soll an dieser Stelle nochmals, dass das Ersetzen sich nicht auf die gespeicherten Datensätze auswirkt.

select nvl(comm,0) as "Kein NULL mehr" from emp;
Kein NULL mehr
--------------
     0
   300
   500
   ...

Es wird in der Spalte comm nach NULL gesucht und durch eine 0 bei der Ausgabe ersetzt.

NVL2()

Mit Hilfe der Funktion NVL2 ist es möglich, abhängig vom Inhalt einer Spalte unterschiedliche Werte zurückzugeben. Hat die Spalte s1 einen Inhalt, so wird s2 zurückgegeben. Ist s1 NULL, so wird s3 zurückgegeben.

nvl2(s1,s2,s2)

select comm,nvl2(comm,'Komm','Keine Komm') from scott.emp;

DECODE()

Durch Decode werden verschiedene Ausdrücke in Abhängigkeit eines Kriteriums ausgewertet. Sie können sich die Funktion DECODE wie eine Art IF ... THEN ... ELSE vorstellen.

select ename as Nachname, 
       job,
       sal as "Altes Gehalt",
       decode (job,'CLERK',sal*0.9,'SALESMAN',sal*0.8,sal) as "Gekürztes Gehalt" 
  from emp
;
NACHNAME  JOB       Altes Gehalt Gekürztes Gehalt
-------------------------------------------------
SMITH     CLERK          800            720
ALLEN     SALESMAN      1600           1280
WARD      SALESMAN      1250           1000
JONES     MANAGER       2975           2975
...

Es wird in der Spalte Job nach clerk gesucht und in diesen Zeilen das Gehalt mit 0.9 multipliziert. Weiterhin wird in der Spalte Job nach salesman gesucht und in diesen Zeilen das Gehalt mit 0.8 multipliziert. Für alle anderen Zeilen gilt, dass das Gehalt ungekürzt ausgegeben wird.

Mathematical Functions

SQL bietet eine Reihe arithmetischer Funktionen. Anhand der folgenden Beispiele soll das Runden ROUND und Abschneiden TRUNC genauer analysiert werden.

ROUND()

select 12345.6789 as Zahl, round(12345.6789,2) as Gerundet 
  from dual
;
ZAHL         GERUNDET
---------------------
12345,6789   12345,68

Die Zahl 12345.6789 wird auf zwei Nachkommastellen gerundet.

select 12345.6789 as Zahl, round(12345.6789,-2) as Gerundet 
  from dual
;
ZAHL        GERUNDET
--------------------
12345,6789     12300

Die Zahl 12345.6789 wird auf zwei Stellen vor dem Komma gerundet.

TRUNC()

select 12345.6789 as Zahl, trunc(12345.6789,2) as Abgeschnitten 
  from dual
;
ZAHL      ABGESCHNITTEN
-----------------------
12345,6789     12345,67

Die Zahl 12345.6789 wird auf zwei Nachkommastellen abgeschnitten.

select 12345.6789 as Zahl, trunc(12345.6789,-2) as Abgeschnitten 
  from dual
;
ZAHL        ABGESCHNITTEN
-------------------------
12345,6789          12300

Die Zahl 12345.6789 wird auf zwei Stellen vor dem Komma abgeschnitten.

Date Functions

Datumsberechnungen

Datumswerte werden in Oracle intern als numerische Werte interpretiert. Hierbei entspricht eine ganze Zahl einem vollen Tag. Die Zahl 0,5 würde als 12 Stunden interpretiert werden, die Zahl 0,75 würde als 18 Stunden interpretiert werden. Bei Berechnungen mit Datumswerten gelten folgende Eigenschaften:

Datum + Zahl  = Datum
Datum - Zahl  = Datum
Datum - Datum = Anzahl der Tage

select sysdate-1 as "Gestern", sysdate as "Heute", sysdate+1 as "Morgen" 
  from dual
;
 Gestern     Heute    Morgen
----------------------------
26.12.03  27.12.03  28.12.03

Es werden die Dati von Gestern, Heute und Morgen ausgegeben.

select sysdate - hiredate as "Tage als Angestellter" from emp;
Tage als Angestellter
---------------------
8091,85933
8026,85933
...

Es wird vom aktuellen Datum (sysdate) das Einstellungsdatum abgezogen. Ausgegeben wird nun die Anzahl von Tagen zwischen dem aktuellen Datum (sysdate) und dem Einstellungsdatum.

MONTHS_BETWEEN()

Mit der Funktion MONTHS_BETWEEN ist es möglich, die Monate zwischen zwei Datumswerten zu ermitteln.

select ename as "Nachname",
       months_between(sysdate,hiredate)/12 as "Jahre als Angestellter" 
  from emp
;
Nachname  Jahre als Angestellter
--------------------------------
SMITH              22,1528973
ALLEN              21,9781661

Es werden die Monate zwischen dem aktuellen Datum (sysdate) und dem Einstellungsdatum ermittelt. Das Ergebnis sind demnach die Monate, die die Person angestellt ist. Dieser Wert wird durch 12 geteilt, und als Ergebnis ist nicht mehr in Monaten, sondern in Jahren.

ADD_MONTHS()

Mit ADD_MONTHS können eine bestimmte Anzahl von Monaten auf ein Datum addiert werden.

select ename as "Nachname",add_months(hiredate,12*25) as "Gehaltserhöhung" 
  from emp
;
Nachname  Gehaltserhöhung
-------------------------
SMITH         17.12.05
ALLEN         20.02.06

Zu dem Einstellungsdatum werden 12*25 Monate, also 25 Jahre, addiert. Dieses Datum wird ausgegeben. Es wird also der Tag 25 Jahre nach dem Einstellungsdatum ausgegeben. In diesem Fall ist an diesem Tag eine Gehaltserhöhung vom Chef geplant.

Übungen 06

Übungen siehe Seite .