✔ ORACLE 10g SQL Hilfe bei Regular Expressions

[StephanHö]

Hallo Forumsmitglieder!

seit ein paar Tagen beschäftigt mich ein Problem mit den regular expression und ich komme nicht voran. Vielleicht kann mir hier jemand auf die Sprünge helfen.

Zum Problem:
Die Datensätze sind alle gleich aufgebaut.

Ein Beispiel:
Text1 Zahl1[A-Z] - {12345} Text2_Zusatz1_Zusatz2

Der Zahl1 können Buchstaben folgen, aber nicht zwangsläufig, insofern macht es Sinn, bis zum ' - ' abzufragen. Dies ginge im Grunde auch (s.u.), die 5stellige Zahl herauszufiltern und von deren Position immer 3 abzuziehen, dann bin ich auch am Ende von Zahl1 unabhängig, ob ein Buchstabe folgt oder nicht.

Den Text1 separiere ich mit:

select substr(text,1,regexp_instr(text,' [0-9]')-1)),

Die -1 killt quasi das Blank am Ende vor der Zahl1.

Bei der Zahl1 wird es nun problematisch:

substr(text,regexp_instr(text,' [0-9]'),regexp_instr(text,' [0-9]{5}')-3)

Der erste Teil startet da, wo der Text1 aufhörte und der zweite Parameter holt sich die Position der immer 5stelligen Zahl.

Als Ergebnis erhalte ich aber nach obigem Datensatzbeispiel: 'Zahl1 - 12345 AD',
während ein

substr(text,regexp_instr(text, '[0-9]{5}'),5)

die 5stellige Zahl korrekt liefert.

Wie ich dann noch den _ abfrage, weil die ja wohl nur über '\_' und escape '\' möglich sein werden und das dann in die regexp einbaue, habe ich noch gar nicht probiert

Bin für alle Lösungsansätze dankbar.

Grüße,

Stephan

PS: sorry für die zurückliegende Unübersichtlichkeit, hatte die code-tags nicht benutzt...

 

[Exceptionfault]

Hallo Stephan,

ist leider etwas schwer die Regex`s ohne konkrete Beispiele aufzubauen. Dein Text1, enthält der Leerzeichen?

Ich bin mal von folgendem Beispiel augegangen (in SQL*Plus nachzuvollziehen):

 var t varchar2(100)
 exec :t := 'EinzusammenhängenderText 98765ABCDE - 12345 EinzweiterText_MitZusatz_UndNocheinZusatz'

Text 1 erhält man mit folgedem Regex:

select regexp_substr( :t, '^\S+' ) from dual;  => EinzusammenhängenderText

Das ^ matcht immer den Anfang einer Zeile bzw. Zeichenkette. \S steht für Zeichenketten.

Die Zahl 1 finden wir mit

select regexp_substr( :t, '[0-9]+([A-Za-z]*)', 1,1 ) from dual; => 98765ABCDE

Und den Rest:

 select regexp_substr( :t, '[0-9]{5}', instr(:t, '-') ) from dual; => 12345
 select regexp_substr( :t, '\S+$' ) from dual; => EinzweiterText_MitZusatz_UndNocheinZusatz

Das setzt natürlich voraus, dass keine Leerzeilen im Text vorkommen. Hoffe das hilft ein bisschen. Bei Problemen mit REGEXP schau ich immer hier nach: http://www.regular-expressions.info/

 

[StephanHö]

Hallo Exceptionfault,

vielen Dank erstmal für die Hinweise, werde denen mal nachgehen und mein Wissen zu erweitern versuchen.

Der Text1 kann Leerzeichen enthalten.

Nimm als Beispiel mal das Folgende:

Straßenname Hausnummer[Zusatz] - Postleitzahl Ort [_Zusatz1[_Zusatz2[_evtl. Zusatz3]]]]

Im Grunde das klassische Zerpflücken einer Adresse mit Zusätzen.
Die Daten zusammenzufügen stelle ich mir da einfacher vor, aber sie zu zerlegen... Hätte nicht gedacht, daß es so heftig wird
Normalerweise liegen derartige Daten auch in getrennten Spalten vor, ist hier aber leider nicht der Fall. Der Datensatzaufbau ist aber in jedem Fall immer gleich, also zwischen Hausnummer und PLZ steht immer ein ' - ' und vor jedem Zusatz immer ein '_'. Sieht einfach aus - dachte ich... da bekommt das Wort Verdacht eine völlig neue Bedeutung

dankende Grüße,

Stephan

 

[Exceptionfault]

Hier mal ein Versuch. Ist nicht wirklich schön, hat für mein Beispiel aber gut funktioniert. Der prozedurale Ansatz ist zwar etwas langsamer, aber etwas freundlicher zu warten, falls sich die Struktur doch mal ändern sollte.

Schau mal ob du damit zurecht kommst, ansonsten einfach wieder melden!

set serveroutput on
declare
	v_string  VARCHAR2(400)	:= 'Schillerstrasse 12b - 12345 Testort _mit zusatz 1 _und noch einem zusatz _und zusatz 3';
	v_strasse VARCHAR2(100);
	v_hausnr  VARCHAR2(100);
	v_plz	  VARCHAR2(5);
	v_ort	  VARCHAR2(100);

	TYPE zusatz_liste IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
	
	v_index	  PLS_INTEGER := 0;
    v_Scan	  BOOLEAN     := true;
	v_start	  PLS_INTEGER := 0;
	v_end	  PLS_INTEGER;
	v_zusatz  zusatz_liste;
begin
	
	v_strasse := regexp_substr( v_string, '^([^0-9]+)' );
	v_string  := replace( v_string, v_strasse, '' );

	v_hausnr  := regexp_substr( v_string, '^([^-]+)' );
	v_string  := replace( v_string, v_hausnr, '' );

	v_plz     := regexp_substr( v_string, '[0-9]{5}' );
	v_string  := replace( v_string, '- ' || v_plz, '' );

	v_ort     := regexp_substr( v_string, '^([^_]+)' );
	v_string  := replace( v_string, v_ort, '' );
	v_ort     := TRIM( v_ort );
	
	WHILE v_Scan = TRUE LOOP
		v_start := INSTR( v_string, '_' );

		IF v_start = 0 THEN 
			v_Scan := false;
		END IF;

		v_end   := INSTR( v_string, '_', v_start+1 );
		IF v_end = 0 THEN
			v_Scan := false;
			v_zusatz(v_index) := substr( v_string, v_start+1 );
	    ELSE
			v_zusatz(v_index) := substr( v_string, v_start+1, v_end-2 );
			v_string := replace( v_string, v_zusatz(v_index) || '_', '' );
			v_index := v_index + 1;
		END IF;
	END LOOP;


	dbms_output.put_line( 'Strasse     : ' || v_strasse );
	dbms_output.put_line( 'Hausnummer  : ' || v_hausnr );
	dbms_output.put_line( 'PLZ         : ' || v_plz );
	dbms_output.put_line( 'ORT         : ' || v_ort );

	FOR i IN v_zusatz.FIRST .. v_zusatz.LAST LOOP
		dbms_output.put_line( 'Zusatz ' || (i+1) || '    : ' || v_zusatz(i) );
	END LOOP;
end;
/

 

[StephanHö]

Hut ab, mein lieber,

Dein Script als solches läuft einwandfrei und völlig exakt.
Aber Du meintest, ich solle mich melden, wenn ich damit so nicht zurechtkomme.

Ich melde mich hiermit

Es läuft ohne Änderung auf Anhieb in SQL+, aaaaber:
es geht ja nur der eine Datensatz. Mein Problem ist, die entsprechenden Datensätze aus der selektierten Tabelle zu übergeben.
Während Du scheinbar auf dem Meeresgrund tauchst, dümpele ich offensichtlich mit herausgestrecktem Hintern an der Oberfläche.

Geht es bitte auch etwas schlichter, damit ich einfältige Seele das auch kapiere?
Probiert habe ich folgendes: Du hast vor jeden Zusatz ein ' ' gesetzt, es geht auch ohne einwandfrei.
Wenn aber kein Zusatz da ist, hilft nur ein kill -9...

Werde aber nochmal Dein Script modifizieren. Mal schauen wie weit ich komme. Melde mich mit Ergebnis

Dankende Grüße,

Stephan

 

[StephanHö]

Hallo Exceptionfault,

habe jetzt mal ein wenig herumprobiert. Bis auf die Zusätze bekomme ich alles per "normalem" Script.
Das Problem bei den Zusätzen ist ja:
ist nur ein '_' vorhanden, muß bis Zeilenende gelesen werden,
bei zweien müssen zwei Zusätze generiert werden, wobei dann der Zweite bis zum Zeilenende gelesen werden muß, bei Dreien analog.

Zufriedenstellend läuft bislang folgender code:

select adr_tab,
regexp_substr(adr_tab,'^([^0-9]+)' )STRASSE,
substr(adr_tab,length(regexp_substr(adr_tab,'^([^0-9]+)' )),length(regexp_substr(adr_tab,'^([^-]+)' ))-length(regexp_substr(adr_tab,'^([^0-9]+)' ))) HNR,
regexp_substr(adr_tab,'[0-9]{5}',instr(adr_tab,'-')) PLZ,
substr(adr_tab,(regexp_instr(adr_tab,'[0-9]{5}')+6),regexp_instr(adr_tab,'\_',1,1)-(regexp_instr(adr_tab,'[0-9]{5}')+6)) GEMEINDE,
from tab2
where info='OBJ'

Ein Problem taucht mittendrin auf. Da meldet SQL:

ORA-01428: argument '0' is out of range

Keine Ahnung, was das bedeutet und welche Ursache es hat. Die Fehlerreferenz ist mir derzeit zu umfangreich. Kann ja nur ein Fehler in der Deklaration o.a. Parameter sein, der aus dem Ruder läuft.

Kann man die Zusätze in der gleichen Form herausholen, wie oben oder muß man da tiefer einsteigen? Performence ist nicht das Problem, solange die Bytes nicht gestanzt werden

Grüße,

Stephan

 

[StephanHö]

Hallo @all

Nun hab' ich's - Vielen Dank an alle, die mir mit Rat und Tat zur Seite gestanden haben, damit eine sinnvolle Lösung erarbeitet werden konnte. Mein besonderer Dank gilt Exceptionfault und uminky aus dem Oracle-Entwicklerforum.

Für alle, die die Lösung interessiert, sei sie hier angegeben:

CREATE OR REPLACE FUNCTION YF_SPLIT (
sTraget IN varchar2 ,
sTrenner IN char,
nReturnNum IN number
) RETURN varchar IS
sResult varchar2(250 ) := '' ;
nOffset1 number(10) := 0 ;
nOffset2 number(10) := 0 ;
nLgTr NUMBER(4) := LENGTH(sTrenner) ;
BEGIN
IF nReturnNum >= 2 THEN
nOffset1 := instr(sTraget,sTrenner,1, nReturnNum - 1) ;
ELSE
nOffset1 := instr(sTraget,sTrenner,1, nReturnNum ) ;
END IF ;
nOffset2 := instr(sTraget,sTrenner,1, nReturnNum) ;
IF nOffset1 = 0 and nReturnNum = 1 THEN
sResult := sTraget ;
ELSIF nOffset1 != 0 and nReturnNum = 1 THEN
sResult := substr(sTraget,1, nOffset1 ) ;
ELSIF nOffset1 = 0 and nReturnNum != 1 THEN
sResult := '' ;
ELSE
IF nOffset2 = 0 THEN
sResult := SUBSTR(sTraget,nOffset1 + nLgTr) ;
ELSE
sResult := SUBSTR(sTraget,nOffset1 + nLgTr,(nOffset2-nOffset1)-1 ) ;
END IF ;
END IF;
RETURN REPLACE(TRIM(sResult),sTrenner,'') ;
END;
/

show errors

SELECT regexp_substr(text_tab,'^([^0-9]+)' ) STRASSE,
substr(text_tab,length(regexp_substr(text_tab,'^([^0-9]+)')),length(regexp_substr(text_tab,'^([^-]+)'))-length(regexp_substr(text_tab,'^([^0-9]+)')))HNR,
regexp_substr(text_tab,'[0-9]{5}') PLZ,
substr(text_tab,(regexp_instr(text_tab,'[0-9]{5}')+6),regexp_instr(text_tab,'\_',1,1)-(regexp_instr(text_tab,'[0-9]{5}')+6))GEMEINDE,
yf_split(yf_split(text_tab,' - ',2),'_',2) ZUSATZ_1,
yf_split(yf_split(text_tab,' - ',2),'_',3) ZUSATZ_2,
yf_split(yf_split(text_tab,' - ',2),'_',4) ZUSATZ_3
FROM tab_dat
/

Ich würde mich freuen, wenn ich mich mal revanchieren kann

Grüße,
Stephan