Teil(e) eines Strings in einem mit TAB & CRLF getrennten Feld extrahieren

JudAD

JudAD

Fleissiger Benutzer
Beiträge
71
Hallo Zusammen,

leider stehe ich wieder vor einem für mich komplexen Problem

Ich habe folgendes Problem:

Ich würde gerne ein Feld (Kenzeichen) einer Tabelle (Artikel) in Abhängigkeit des Vorkommens der Artikelnummer in einem Feld (Alternativen) einer anderen Tabelle (Material) aktualisieren.

Das Problem ist, dass die Daten im Feld "Alternativen" nicht als einzelne Datensätze, sondern getrennt mit TAB+TAB & CR+LF in einem Datensatz vorliegen (das ist zwar ein völlig idiotisches Datenbankdesign - aber das hab nicht ich verbrochen). Es kommt immer erst die Artikelnummer gefolgt von 2 TAB [sprich CHAR(9)+CHAR(9)] dannach kommt eine Mengenangabe gefolgt von CR+LF [sprich CHAR(13)+CHAR(10)] was das Ende eines Datensazuens markiert. Das kann dann innerhalb eines Datensatzen n-Mal vorkommen. Unten habe ich mal 3 Zeilen des Feldes dargestellt. Hier kann man sehen das es sowohl Datensätze gibt die nur einen Eintrag aus Artikelnummer + Menge haben oder auch im letzten Datensatz 4.

200056 23.550000
100028 23.550000 200056 23.550000
102514 7.800000 109252 7.800000 207283 7.800000 107284 7.800000

Nun würde ich gerne in der Tabelle Artikel das Feld Kennzeichen mit dem Wert "BAUGRUPPE" befüllen wenn die Artikelnummer im Feld Alternativen der Tabelle Material vorkommt und die Artikelnummer mit dem Wert 2 beginnt.

Vereinfacht:

update Artikel set Kennzeichen = 'BAUGRUPPE' where Artikelnummer in (select Alternativen from Material) and LEFT(Artikelnummer,1) = '2'

Dass das so nicht funktioniert ist mir klar - aber ich habe keine Ahnung wie ich das machen kann.

DANKE VORAB!
 
Werbung:
So sind die Datensätze gespeichert, wenn man die Trennzeichen anzeigen würde ;-)

200056CHAR(9)+CHAR(9)23.550000CHAR(13)+CHAR(10)
100028CHAR(9)+CHAR(9)23.550000CHAR(13)+CHAR(10)200056CHAR(9)+CHAR(9)23.550000CHAR(13)+CHAR(10)
102514CHAR(9)+CHAR(9)7.800000CHAR(13)+CHAR(10)109252CHAR(9)+CHAR(9)7.800000CHAR(13)+CHAR(10)207283CHAR(9)+CHAR(9)7.800000CHAR(13)+CHAR(10)107284CHAR(9)+CHAR(9)7.800000CHAR(13)+CHAR(10)
 
Bin etwas im Stress daher nur eine kurze Antwort:

Ich würde die "Alternativen" zerlegen und daraus eine Art 1:N Tabelle erzeugen. Damit könnte man sauber alle gewünschten Operationen durchführen.

Das ließe sich grundsätzlich mit CTE machen. Dabei würde man den String immer bei einer bestimmten Zeichenfolge (das können ja auch Umbrüche sein) abgeschnitten und der Rest würde wieder eine neue Zeile ergeben. Falls du das nicht hin kriegst schreibe ich das mal in ein paar Tagen auf. Mit MSSQL 2016 scheint es was neues zu geben:
STRING_SPLIT (Transact-SQL)
Habe ich aber noch nicht ausprobiert.
 
Hi und Danke erst mal für die Antwort. Ich habe noch SQL Server 2008R2. CTE muss ich mal ansehen. Eine Auslagerung auf eine temporäre Tabelle wollte ich eigentlich vermeiden. Da ich aber heute Krank zu Hause liege werde ich es erst zum Wochenende hin testen können. Wenn jemand noch eine andere Idee hat, bin ch über jeden Hinweis dankbar.
 
Nein eine "temporäre Tabelle" würde es bei CTE nicht geben. Hier mal ein Beispiel:
Code: 
WITH idiocracy(id,spalte) AS (
   SELECT   newid(),
           '200056' + CHAR(9) + CHAR(9) + '23.550000' + CHAR(13) + CHAR(10) +
           '100028' + CHAR(9) + CHAR(9) + '23.550000' + CHAR(13) + CHAR(10)
   ), temp(id,spalte,rest) AS (
   SELECT   id,
           (   CASE
               WHEN   spalte LIKE '%' + CHAR(13) + CHAR(10) + '[0-9]%'
               THEN   left(spalte,patindex('%' + CHAR(13) + CHAR(10) + '[0-9]%',spalte)+1)
               ELSE   spalte
               END ),
           (   CASE
               WHEN   spalte LIKE '%' + CHAR(13) + CHAR(10) + '[0-9]%'
               THEN   right(spalte,datalength(spalte)-patindex('%' + CHAR(13) + CHAR(10) + '[0-9]%',spalte)-1)
               ELSE   NULL
               END )
   FROM   idiocracy
   UNION ALL
   SELECT   id,
           (   CASE
               WHEN   rest LIKE '%' + CHAR(13) + CHAR(10) + '[0-9]%'
               THEN   left(rest,patindex('%' + CHAR(13) + CHAR(10) + '[0-9]%',rest)+1)
               ELSE   rest
               END ),
           (   CASE
               WHEN   rest LIKE '%' + CHAR(13) + CHAR(10) + '[0-9]%'
               THEN   right(rest,datalength(rest)-patindex('%' + CHAR(13) + CHAR(10) + '[0-9]%',rest)-1)
               ELSE   NULL
               END )
   FROM   temp
   WHERE   rest IS NOT NULL
   )
SELECT   id,spalte
FROM   temp
Die Tabelle "idiocracy" stellt vereinfacht deinen Ausgangszustand dar. Ein Datensatz mit id und eine Spalte mit mehreren Zeilenumbrüchen die eigentlich Datensätze trennen. Die Tabelle "temp" zerlegt die Zeichenkette immer in den ersten Datensatz und gibt den Rest weiter (mit Hilfe von CTE), sie durchläuft den String also eigentlich rekursiv und trennt ihn in einzelne Zeilen auf. Das kannst du dann Joinen, gruppieren, aggregieren wie du willst.
 
Hi, erst mal Danke für dieses Umfangreiche Besipiel.

Muss ich testen wenn ich wieder fit bin. Gebe Dir am Montag Bescheid!
 
ukulele schrieb:
Nein eine "temporäre Tabelle" würde es bei CTE nicht geben. Hier mal ein Beispiel:
Code: 
WITH idiocracy(id,spalte) AS (
   SELECT   newid(),
           '200056' + CHAR(9) + CHAR(9) + '23.550000' + CHAR(13) + CHAR(10) +
           '100028' + CHAR(9) + CHAR(9) + '23.550000' + CHAR(13) + CHAR(10)
   ), temp(id,spalte,rest) AS (
   SELECT   id,
           (   CASE
               WHEN   spalte LIKE '%' + CHAR(13) + CHAR(10) + '[0-9]%'
               THEN   left(spalte,patindex('%' + CHAR(13) + CHAR(10) + '[0-9]%',spalte)+1)
               ELSE   spalte
               END ),
           (   CASE
               WHEN   spalte LIKE '%' + CHAR(13) + CHAR(10) + '[0-9]%'
               THEN   right(spalte,datalength(spalte)-patindex('%' + CHAR(13) + CHAR(10) + '[0-9]%',spalte)-1)
               ELSE   NULL
               END )
   FROM   idiocracy
   UNION ALL
   SELECT   id,
           (   CASE
               WHEN   rest LIKE '%' + CHAR(13) + CHAR(10) + '[0-9]%'
               THEN   left(rest,patindex('%' + CHAR(13) + CHAR(10) + '[0-9]%',rest)+1)
               ELSE   rest
               END ),
           (   CASE
               WHEN   rest LIKE '%' + CHAR(13) + CHAR(10) + '[0-9]%'
               THEN   right(rest,datalength(rest)-patindex('%' + CHAR(13) + CHAR(10) + '[0-9]%',rest)-1)
               ELSE   NULL
               END )
   FROM   temp
   WHERE   rest IS NOT NULL
   )
SELECT   id,spalte
FROM   temp
Die Tabelle "idiocracy" stellt vereinfacht deinen Ausgangszustand dar. Ein Datensatz mit id und eine Spalte mit mehreren Zeilenumbrüchen die eigentlich Datensätze trennen. Die Tabelle "temp" zerlegt die Zeichenkette immer in den ersten Datensatz und gibt den Rest weiter (mit Hilfe von CTE), sie durchläuft den String also eigentlich rekursiv und trennt ihn in einzelne Zeilen auf. Das kannst du dann Joinen, gruppieren, aggregieren wie du willst.
Zum Vergrößern anklicken....

Hi ukulele & sorry für die späte Rückmeldung, aber leider hat sich meine Krankheit etwas länger hingezogen als erwartet.

Heute bin ich wieder bei der Arbeit und wollte mal Dein Script auf meine Tabelle anwenden, scheitere aber leider daran die Spalten (itemcode, alternativmaterial) aus der Tabelle (beas_stl) in das Script zu integrieren. In der Spalte "alternativmaterial" steckt der String der aufgeteilt werden sollte.

Wenn Du nochmal Zeit hättest wäre ich Dir sehr dankbar wenn Du mir Dein Beispiel noch auf meine Tabelle und Spaltennamen anpassen könntest - statt dem String , denn irgendwie kann ich Dein Beispiel nicht auflösen ;-)

Danke vorab
 
Code: 
WITH temp(itemcode,alternativmaterial,rest) AS (
   SELECT   itemcode,
           (   CASE
               WHEN   alternativmaterial LIKE '%' + CHAR(13) + CHAR(10) + '[0-9]%'
               THEN   left(alternativmaterial,patindex('%' + CHAR(13) + CHAR(10) + '[0-9]%',alternativmaterial)+1)
               ELSE   alternativmaterial
               END ),
           (   CASE
               WHEN   alternativmaterial LIKE '%' + CHAR(13) + CHAR(10) + '[0-9]%'
               THEN   right(alternativmaterial,datalength(alternativmaterial)-patindex('%' + CHAR(13) + CHAR(10) + '[0-9]%',alternativmaterial)-1)
               ELSE   NULL
               END )
   FROM   beas_stl
   UNION ALL
   SELECT   itemcode,
           (   CASE
               WHEN   rest LIKE '%' + CHAR(13) + CHAR(10) + '[0-9]%'
               THEN   left(rest,patindex('%' + CHAR(13) + CHAR(10) + '[0-9]%',rest)+1)
               ELSE   rest
               END ),
           (   CASE
               WHEN   rest LIKE '%' + CHAR(13) + CHAR(10) + '[0-9]%'
               THEN   right(rest,datalength(rest)-patindex('%' + CHAR(13) + CHAR(10) + '[0-9]%',rest)-1)
               ELSE   NULL
               END )
   FROM   temp
   WHERE   rest IS NOT NULL
   )
SELECT   itemcode,alternativmaterial
FROM   temp
 
Wow Super, vielen Dank!

bekomme aber nund folgende Fehlermeldung:

Meldung 530, Ebene 16, Status 1, Zeile 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion
 
Dachte ich, aber mir schein es eher eine Endlosschleife zu ergeben - nach über 2 Mio Datensätze habe ich es gestoppt. Hatte die option "option (maxrecursion 0)" angegeben
 
Hm seltsam, ich würde jetzt erwarten das sich keine so großen Einträge darin befinden. Du kannst ja mal schauen was
Code: 
SELECT TOP 10 * FROM beas_stl ORDER BY datalength(alternativmaterial) - datalength(replace,alternativmaterial,CHAR(13) + CHAR(10),''))
so ergibt das müssten die Datensatz mit den meisten Durchläufen sein, danach kann man maximum recursion auch angeben oder eben die großen Broken vorher Filtern.
 
Hi ukulele,

Sorry, konnte gestern nicht mehr weitermachen, da mein Kollege versehentlich Daten gelöscht hatte und ich einzelne Datensätze aus einer Datensicherung wiederherstellen musste.

Deine Anweisung schmeißt zwar einen Fehler raus (The datalength function requires 1 argument(s).), aber unabhängig davon ist die Maximale Zeichenlänge in diesem Feld 108 Zeichen. Ich hatte das mal mit folgender Anweisung verglichen. Ich hatte das gemacht um herauszufiunden wie die Datensätze getrennt wurden.

select itemcode, alternativmaterial,
RTRIM(REPLACE(REPLACE(REPLACE(cast(alternativmaterial as nvarchar(max)),CHAR(9),'x'),CHAR(10),'y'),CHAR(13),'z')) as GE,
LEN(CAST(alternativmaterial AS NVARCHAR(MAX))) as LängeOhneTRIM,
LEN(RTRIM(REPLACE(REPLACE(REPLACE(cast(alternativmaterial as nvarchar(max)),CHAR(9),''),CHAR(10),''),CHAR(13),''))) as GETRIMMT
from beas_stl
where alternativmaterial != '' and alternativmaterial is not null

Was könnte die Ursache mit diesem Rekursionsfehler bzw. Endlosschleife sein?

Es sind auch in der Tabelle ca. 500 Datensätze die im Feld "alternativmaterial" einen Eintrag haben. Also eigentlich alles überschaubar

Danke vorab
 
Ich habe Komma und Klammer verwechselt:
Code: 
SELECT TOP 10 * FROM beas_stl ORDER BY datalength(alternativmaterial) - datalength(replace(alternativmaterial,CHAR(13) + CHAR(10),''))

Ansonsten schwer zu sagen ohne deinen Code zu kennen. Ist die Spalte "rest" eventuell nie NULL oder fehlt die WHERE-Bedingung? Du musst einfach nur einen Datensatz nehmen und Schrittweise zerlegen.
 
Werbung:
Hi,

ich habe Dein korrigiertes Script (datalength) laufen lassen, das wirft 10 Datensätze aus bei denen der Wert im Feld "alternativmaterial" immer NULL ist - komisch?

Dann habe ich die Tabelle kopiert und mal bis auf einen Datensatz (den längsten) alles gelöscht und Dein Script ausgeführt - habe da wieder den selber Fehler (recursion....).

Dann habe ich das ganze mit einem Datensatz getestet wo es nur 1 Datensatz in "alternativmaterial" - da klappt es. Sobald in "alternativmaterial" mehr als 1 Datensatz entahlten ist kommt der Fehler mit recursion...

Ich habe auch mal versucht nur den ersten teil Deines Scriptes (vor dem UNION) auszuführen, und festgestellt, dass wenn in "alternativmaterial" mehr als ein Datensatz enthalten ist, dieser nicht abgeschnitten wurde, sondern im String enhalten blieb (siehe Ergebnis als Anhang). Ob es damit was zu tun hat, weiß ich nicht, denn bei Deinem ersten Entwurf mit Eingabe des Strings hatte s ja auch funktioniert.

Ich bin ratlos :-(
 

Anhänge

  • Teil1.JPG
    Teil1.JPG
    71,7 KB · Aufrufe: 9
Zurück
Oben