[SQL/Oracle] check varchar2 is number

[leeke1984]

Beste mensen

Ik zoek een of er een functie is die kan bepalen of de waarde in een varchar2 veld numeriek is. Er zijn nu bijvoorbeeld documentid's die door een fout 1400m2 heten. Ik wil uiteindelijk het hoogste documentid weten die in een aantal tabellen kan voorkomen. Ik weet dat het raar is dat de database zo is gemaakt, maar daar heb ik geen invloed op en moet het hier mee doen. Wie kent er zo'n functie. De database is een oracle 9 database.

[RobIII]

Buiten het feit dat er natuurlijk nooit een string in een docid terecht had mogen komen (maar ik geloof je dan maar op je woord dat je daar niets aan kunt doen...):

SQL:

Select max(docid) from mytable where LENGTH(TRIM(TRANSLATE(docid, ' 0123456789',' '))) is null;

3 seconden googlen -> voila

[ Voor 58% gewijzigd door RobIII op 25-03-2008 16:51 ]

[Niemand_Anders]

Waarom is jouw ID veld als die numeriek moet zijn als TEKST gedefineerd? Waarom maak je er geen int van? Dan kan er alleen een nummer in.

Valt denk ik onder de noemer basis logica..

[leeke1984]

@niemand_anders: Ja dat vragen wij ons ook af, maar ik zet er niet voor niets bij dat ik GEEN invloed heb op de databasestructuur. Deze ligt vast en kan niet veranderd worden. Ik vind dat ook stom, maarja ik heb het dan ook niet gemaakt ;-)

Ja deze hadden wij ook bedacht, maar we hoopten op iets efficienters middels een standaard oraclefunctie. Maar toch bedankt! ;-) en mijn excuses voor het niet toevoegen!

[ Voor 53% gewijzigd door leeke1984 op 25-03-2008 16:43 . Reden: toevoeging ]

[RobIII]

leeke1984 schreef op dinsdag 25 maart 2008 @ 16:39:
Ja deze hadden wij ook bedacht,

Als je dat dan voortaan even vermeldt in je topicstart (zoals ook beschreven in onze Programming Beleid Quickstart) dan hoef ik mijn tijd niet te gaan zitten verdoen met oplossingen die je zelf ook al had verzonnen

[leuk_he]

RobIII schreef op dinsdag 25 maart 2008 @ 16:36:

SQL:

Select max(docid) from mytable where LENGTH(TRIM(TRANSLATE(docid, ' 0123456789',' '))) is null;

Werkt voor 99%.

code:

select 1e7 /100 from dual;

zijn ook geldige nummers. Enige manier om het 100%goed to doen is met een taal (plsql) record voor record naar een nummer om te zetten en excepties af te vangen waar het niet lukt.

[RobIII]

leuk_he schreef op dinsdag 25 maart 2008 @ 16:44:
[...]


Werkt voor 99%.

code:

select 1e7 /100 from dual;

zijn ook geldige nummers.

Wellicht geldige nummers, maar geen geldig ID lijkt me (uiteraard; 'voluit' of '1e7' is hetzelfde). Hoe dan ook lijkt het me weinig relevant; als dat in je docid veld staat is dat nog je kleinste probleem.

[ Voor 21% gewijzigd door RobIII op 25-03-2008 16:49 ]

[Niemand_Anders]

Even over een andere boeg, als je het database probleem niet kunt verhelpen, maar 'fix' je het probleem dan niet in je applicatie. Immers alle data welke je naar de database schrijft behoort je te valideren.

Wordt de database door meerdere applicaties gebruikt (en gevuld) dan zul je ook de resultset van de database moeten controleren. Op het moment dat je applicatie docid niet kan omzetten naar een nummer, dan kun je een error raisen of op een andere manier proberen het probleem te verhelpen.

[DigiK-oz]

Je kan een functie maken die aangeeft of een varchar een valide nummer is :

CREATE OR REPLACE FUNCTION IS_NUMERIC (p_num IN VARCHAR2) RETURN NUMBER AS
l_tst NUMBER;
BEGIN
l_tst := TO_NUMBER(p_num);
RETURN 1;
EXCEPTION WHEN OTHERS THEN RETURN 0;
END;
/

En dan iets als

SELECT DOCID,IS_NUMERIC(DOCID) FROM TABEL;