Check Constraint - Prüfen auf NUMBER

[JRThefrog]

Ich habe folgendes Problem, über das ich mir nun schon seit Stunden das Hirn zermartere. Entweder mein Hirn ist bereits leicht geschwächt, oder die Sache ist tatsächlich nicht so trivial, wiewohl man meinen könnte, sowas schafft Oracle im Handumdrehen:
Ich habe in einer Tabelle ein VARCHAR-Feld; es soll durch einen Check-Constraint sichergestellt werden, daß dort aber NUR Ziffern reinkommen. Mit to_number geht das nicht; eine Erweiterung der Tabelle um eine "function"-Feld verbietet sich von der Applikation her, die diese Tabelle verwendet. Eine selbstdefinierte PL/SQL-Funktion kann ich wiederum in einem Check-constraint nicht verwenden. Gibt es hier eine einfache Lösung - ohne mit translate zu arbeiten?
Vielleicht gibt's ja hier eine zündende Idee!
LG
JR
Quak! :-)

[Falk Prüfer]

Hallo,

warum sollte dafür TO_NUMBER nicht geeignet sein? Wenn du z.B. in einem BEFORE INSERT OR UPDATE - Trigger versuchst den Wert von :NEW.VarcharNumberFeld mit TO_NUMBER in eine Zahl umzuwandeln, dann würde die evtl. ausgelöste Exception den Update- bzw. Insertvorgang scheitern lassen.

Ganz am Rande wäre es natürlich sehr viel sinnvoller für ein Feld, welches nur numerische Werte beinhalten soll, auch einen numerischen Datentyp zu verwenden.

Gruß Falk

[uminky]

Ein To_number ist nicht geeignet (nicht immer). Es sei den "1-5" ist eine Zahl für dich. Mit Hochkomma drum ist es keine Zahl ohne schon:

select to_number(1-5) from dual;

Ich würde so vorgehen:
erst prüfen ob das erste Zeichen ein Vorzeichen ist und danach alle zahlen und den Dezimalpunkt aus der Zeichenkette entfernen. Dann sollte ein TRIM is Null True ergeben.

select translate('-4424821','0123456789.',' ') from dual;

[JRThefrog]

Hallo uminky,

danke für den erlösenden Tip; ich hatte einen Knoten im Hirn wegen der translate-Funktion; ich mache das jetzt so:
length(trim(translate(fax,'0123456789','__________ '))) = 0 and not (length(trim(fax)) is NULL and fax is not NULL) - die Unterstriche stehen hier für Spaces. Der zweite Teil des Ausdrucks deshalb, weil sonst ein String mit nur Leerzeichen möglich wäre. Der Constraint funktioniert bestens.

Falk,

danke für den Tip. Aber wie gesagt wollte ich das mit einem Check-constraint, nicht mit einem Trigger lösen. Numerisches Feld hat die Applikation deshalb nicht, weil es eine Faxnummer ist, wo auch + und / und Kommentare eingetragen werden können sollen. Da unser Faxserver sich des öfteren mit solchen NICHT-Ziffern aufhängt, haben wir nun beschlossen, daß dort auch NUR mehr Ziffern sein sollen (abgesehen davon wäre eine führende 0 bei einem numerischen Feld ja nicht möglich (z. B. 00431...)).
Danke euch jedenfalls!
LG
JR

[Falk Prüfer]

...(abgesehen davon wäre eine führende 0 bei einem numerischen Feld ja nicht möglich (z. B. 00431...)).
...

Das ist richtig, obwohl es für den konkreten Fall einer Fax- bzw. Telefonnr. keine Rolle spielt . Meines Wissens gibt es keine Telefonnummer und auch keine Vorwahl, die mit einer 0 beginnt. Die führende(n) Null(en) sind i.a.R. der Erkennung einer Amts-, Orts-, Landes- oder sonstigen Vorwahl vorbehalten. Dies ist jedoch u.U. von der Telefonanlage, vom Standort und vom Telefonprovider abhänging. In jedem Fall sind die führenden Nullen NICHT Bestandteil der Telefonnummer und sollten "eigentlich" auch nicht dort gespeichert werden - aber das nur am Rande und als kleinen "philosophischen" Einwurf.

Gruß Falk

[JRThefrog]

Also wenn ich 512xxxxxxx wähle, lande ich in Wien, im 5. Bezirk; wähle ich aber 0512xxxxxx, dann lande ich in Innsbruck. Wie soll ich das wohl ohne führende Null unterscheiden?? Natürlich könnte ich jede Nummer kompletten inkl. Landeskennzahl angeben; es soll aber doch auch ohne Landeskennzahl funktionieren, bei ca. 60 % der Nummern im Inland. BTW, die führende 0 ist ja nur EIN Ausschlußkriterium für eine echte TO_NUMBER Überprüfung; denn ein dort erlaubtes Dezimalkomma wäre auch nicht erwünscht (ich habe das im Titel eigentlich mißverständlich - oder eig.: falsch - ausgedrückt: Prüfen auf NUMBER; es müßte eigentlich, wie im ersten Posting steht, "Prüfen auf NUR Ziffern" o. ä. heißen.
LG
JR

[Falk Prüfer]

Also wenn ich 0512xxxxx wähle, dann lande ich irgendwo in Hildesheim und nicht in Innsbruck
Wenn die Datenbank lediglich als Input für einen Faxserver dient, dann halte ich es für völlig legitim die Nummern so zu speichern, wie sie gewählt werden müssen. Worauf ich mit meiner Randglosse hinweisen wollte ist die logische Trennung von Vorwahl(en) und Telefonnnumer in z.B. einer Adressverwaltung. Das hinzufügen der technisch notwendigen 0 bzw. + oder eines Pausenzeichens sollte der jeweiligen Anwendung vorbehalten und dort konfigurierbar sein.

Gruß Falk