Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

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

Dieses Thema im Forum "Microsoft SQL Server" wurde erstellt von JudAD, 18 September 2018.

  1. JudAD

    JudAD Fleissiger Benutzer

    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!
     
  2. JudAD

    JudAD Fleissiger Benutzer

    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)
     
  3. ukulele

    ukulele Datenbank-Guru

    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.
     
  4. JudAD

    JudAD Fleissiger Benutzer

    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.
     
  5. ukulele

    ukulele Datenbank-Guru

    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.
     
    Walter gefällt das.
  6. JudAD

    JudAD Fleissiger Benutzer

    Hi, erst mal Danke für dieses Umfangreiche Besipiel.

    Muss ich testen wenn ich wieder fit bin. Gebe Dir am Montag Bescheid!
     
  7. JudAD

    JudAD Fleissiger Benutzer

    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
     
  8. ukulele

    ukulele Datenbank-Guru

    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
     
  9. JudAD

    JudAD Fleissiger Benutzer

    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
     
  10. JudAD

    JudAD Fleissiger Benutzer

    OK, gelöst!
     
  11. JudAD

    JudAD Fleissiger Benutzer

    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
     
  12. ukulele

    ukulele Datenbank-Guru

    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.
     
  13. JudAD

    JudAD Fleissiger Benutzer

    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
     
  14. ukulele

    ukulele Datenbank-Guru

    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.
     
  15. JudAD

    JudAD Fleissiger Benutzer

    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:

Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden