ORACLE PL-SQL vraag

[Verwijderd]

Hallo,

Ik heb een kolom in een tabel leegstand die eigenlijk een samenvoeging is van een huisnummer en (eventueel) een huisletter. Het nummer en de huisletter wil ik uit elkaar halen met de Oracle functie replace.

Voorbeeld

ADRES
--------------------------------------------------
18
11
6a
7
13
9a


SQL> update leegstand
2 set adres=replace(adres,'a',' __________a');

Resultaat:

ADRES
--------------------------------------------------
18
11
6_________a
7
13
9_________a

P.S. ik werk even met de underscore, omdat spaties blijkbaar niet worden weergegeven. Lees voor de __ spaties. Het resultaat is dus dat als er een letter voorkomt deze wordt vervangen door dezelfde letter voorafgegaan door een aantal spaties.

Hierna kan ik met select to_number(ltrim(substr(adres,1,5))) from leegstand het nummer en met select rtrim(substr(adres,5) de huisletter er uit halen en in aparte kolommen zetten.

Ik wil echter een pl-sql maken die dit voor alle letters doet. Huisletters varieren van a t/m z, dus voor al deze letters moet de bovenstaande replace worden gedaan.

Ik ben helemaal geen pl-sql man, maar ik heb het toch geprobeerd:

SQL> get pl_nummer
1 declare letter varchar(1);
2 begin
3 while letter in ('a','b','c') -- Even een testje van drie letters
4 loop
5 update leegstand
6 set adres=replace(adres,letter,'_________letter'); -- __ zijn spaties
7 commit;
8 end loop;
9* end;
10

Maar dit werkt niet.

Is er hier een ervaren PL-SQL-oracle man die het wel weet?

BVD.

[justmental]

Zoals gewoonlijk ga ik 'em niet helemaal voorkauwen, maar ik help je wel op weg
Als er een huisletter is dan is dat altijd het laatste teken.
Als het gegeven numeriek is dan staat er geen huisletter in.

een test of iets numeriek is kun je met een exta pl/sql block binnen je programmablok doen:

code:

begin
  begin
    v_blabla := to_number (kolomwaarde);
  exception
    when value_error
    then
      -- hier laatste letter erafknippen
  end;
  -- hier wegschrijven
end;

De laatste letter erafknippen kan eenvoudig met een substr en length.

[Verwijderd]

justmental schreef op 17 november 2002 @ 18:41:
Zoals gewoonlijk ga ik 'em niet helemaal voorkauwen, maar ik help je wel op weg
Als er een huisletter is dan is dat altijd het laatste teken.
Als het gegeven numeriek is dan staat er geen huisletter in.

een test of iets numeriek is kun je met een exta pl/sql block binnen je programmablok doen:

code:

begin
  begin
    v_blabla := to_number (kolomwaarde);
  exception
    when value_error
    then
      -- hier laatste letter erafknippen
  end;
  -- hier wegschrijven
end;

De laatste letter erafknippen kan eenvoudig met een substr en length.

Thanks, zal het eens proberen.

[Goodielover]

Gebruik de aanpak van justmental en roep in de exception handling weer de procedure aan.
Dan kan je ook het huisnummer "3 (tweede verdieping rechts)" aan. Dan blijf je strippen tot je to_number goed gaat.

Een alternatief is een gebruikmaken van de translate funtie:

code:

translate(huinr," abcdefghijklmnopqrstuvwxyz()-"," ")

Deze functie stript alle letters uit een huisnr. Eventueel zet een lower() op het huisnr om zeker te zijn dat alle letters weg zijn.

[Verwijderd]

Goodielover schreef op 18 november 2002 @ 10:05:
Gebruik de aanpak van justmental en roep in de exception handling weer de procedure aan.
Dan kan je ook het huisnummer "3 (tweede verdieping rechts)" aan. Dan blijf je strippen tot je to_number goed gaat.

Een alternatief is een gebruikmaken van de translate funtie:

code:

translate(huinr," abcdefghijklmnopqrstuvwxyz()-"," ")

Deze functie stript alle letters uit een huisnr. Eventueel zet een lower() op het huisnr om zeker te zijn dat alle letters weg zijn.

Briljant! Dit werkt. Nu kan ik zonder pl-sql dit voor elkaar krijgen.

Ik heb aan de tabel leegstand eerst twee kolommen toegevoegd:

SQL> desc leegstand
Naam___________________Null? Type
--------------------------_______ --------__ ------------------------
ADRES__________________________VARCHAR2(50)
STRAAT__________________________VARCHAR2(24)
HUISNR__________________________NUMBER(4)
HUISLT___________________________CHAR(1)


Daarna onderstaand script om huisnr te vullen.

update leegstand
set huisnr=to_number(ltrim((rtrim((translate(adres,'abcdefghijklmnopqrstuvwxyz',' '))))))
/


Vervolgens het veld huisletter vullen met:

update leegstand
set huislt=(ltrim((translate(adres,1234567890,' '))))
/

Nu heb ik wat ik hebben moet.

Die methode van Justmental ga ik ook nog eens proberen.

[Goodielover]

gebruik van ltrim en rtrim in de to_number is niet nodig. Oracle trimt zelf wel.
Bedenk wel dat er in Enschede bijvoorbeeld een straat is die "Boulevard 1945" heet.
Op deze manier worden de huisnummers dus niet goed weergegeven.