Manchmal kommt es vor, dass man innerhalb einer SQL-Query nur die numerischen Werte ausgeben will. Gut grundsätzlich vermeidet man sowas von Anfang an, indem man die Spalten als numerisch definiert und alle anderen Werte beim Einfügen abweist. Aber manchmal kommt es halt vor, dass man solche komischen Sachen machen muss.
CREATE TABLE tabelle(a varchar2);
insert into tabelle values('a');
insert into tabelle values('a10');
insert into tabelle values(' 10');
insert into tabelle values('100');
insert into tabelle values('200');
Ab Oracle 10, gibt es die Funktion regexp_like, mit der man einfach auf einen regulären Ausdruck prüfen kann. Das sollte dann in etwas so aussehen (kein Oracle 10 zur Verfügung um zu testen, dass es geht):
select a from tabelle where regexp_like(a,'^[[:digit:]]+$')
Lustig wird es aber, wenn man eine ältere Oracle Version verwendet.
Prinzipiell gibt es zwei Möglichkeiten:
- man benutzt Translate:
select a from tabelle where translate(a,' 0123456789', '#') is null
(Man beachte das Leerzeichen!)
Ich habe damit jetzt eine Weile rumgespielt, aber irgendwie habe ich gerade Probleme, die Logik zu verstehen. Ich verstehe zum Beispiel nicht, warum dort ein führendes Leerzeichen stehen muß. - man nimmt to_number(char)
Das funktioniert aus offensichtlichen Gründen nicht ;)
Wer es nicht glaubt:
select to_number('a') from dual
und erfreut sich der Fehlermeldung "ORA-01722: invalid number" - Man schreibt eine Funktion/Prozedur und testet selber auf Zahlen:
- Die offensichtliche Variante ist:
create or replace function isnumeric (p_string in varchar2)
return boolean
as
l_number number;
begin
l_number := p_string;
return TRUE;
exception
when others then
return FALSE;
end;
/
Dann führt man aus
SQL> select a from tabelle where isnumeric(a);
select a from tabelle where isnumeric(a)
*
ERROR at line 1:
ORA-00920: invalid relational operator
und wundert sich warum es nicht funktioniert.
Der Grund ist, SQL kennt keinen Datentyp boolean, also kann es auch mit dem angegebenen Rückgabewert nichts anfangen. - Man verändert die Funktion folgendermaßen:
create or replace function isnumeric (p_string in varchar2)
return varchar2
as
l_number number;
begin
l_number := p_string;
return l_number;
exception
when others then
return null;
end;
/
Jetzt gibt die Funktion halt alle Zahlen zurück oder null, wenn es keine Zahl ist.
SQL> select * from tabelle where isnumeric(a) is not null;
A
------------------------------
10
100
200
Ich habe diesem Dauerbrenner-Thema auch einen Artikel gewidmet, in dem ich noch auf die "Old School"-Methode mit Translage zurückgreife und auch eine Möglichkeit zur Bereinigung aufzeige:
http://oraculix.wordpress.com/2008/12/11/immer-wieder-ora-01722-invalid-number/
Meine Zustimmung: Schön ist das alles nicht, aber aus einem Krautkopf kann man nun mal keinen Blumenkohl machen - schöner ist eine richtige Typisierung der Daten.
Viele Grüße,
Uwe
Schöner Artikel übrigens.
as
begin
RETURN TO_NUMBER (p_string);
exception
when others then
return null;
end;