none
dynamisches SUBSTRING bzw. Reduzierung/Extrahieren einer Zeichenkette über 2 Felder möglich? RRS feed

  • Frage

  • Hallo zusammen,

    ich habe eine Belegnummer (Fremdbelegsnummer + ev. Bestellnummer) an der aber nur teilweise die Bestellnummer angehängt ist. Die Bstellnummer wird zusätzlich in einem extra Feld ausgewiesen. 

    Bsp.: Belegnummer: 6644619-21001129   Bestellnummer: 21001129
    Bsp.: Belegnummer: 1234567-0185.FL Bestellnummer: isNull

    Die Bestellnummer ist zwar IMMER identisch mit Länge und Art und Trennung in der Belegnummer enthalten, aber die Fremdbelegsnummer kann völlig unterschiedlich lang sein.

    Gibt einen SQL-Statement mit dem man die Bestellnummer quasi als "Rattenschwanz" von der Belegnummer einfach abschneiden kann, sofern im Feld Bestellnummer diese vermerkt ist? Wer hat eine Idee die dynamische Länge & Art der Fremdbelegs zu extrahieren (schneide rechts von der Belegnummer 9 Zeichen ab, wenn Bestellnummer isNotNull)

    Vielen Dank.


    Donnerstag, 14. Oktober 2021 10:07

Antworten

  • Hi,

    auch hier (wieder) der Hinweis, dass es immer sinnvoll und für die Antwortenden (und daher letztendlich auch für dich selbst) sehr hilfreich ist, wenn Du die Tabellendefinition als CREATE TABLE oder DECLARE @... TABLE Statements, Beispieldaten als INSERT INTO Statements und die gewünschten Ergebnisse, exakt auf den genannten Beispieldaten basierend, posten würdest. Bitte zukünftig dran denken!

    In deinem Fall ist der Bindestrich doch der Trenner. Das wäre doch dein Zeichen, an dem Du dich orientieren kannst.

    DECLARE @Belege TABLE( Belegnummer nvarchar( 255 ), Bestellnummer nvarchar( 255 ) );
    
    INSERT INTO @Belege( Belegnummer, Bestellnummer )
    VALUES ( 'A1111-111111',  '111111' ),
           ( 'A2222-EXT22222', NULL ),
           ( 'A3333-333333',  '333333' ),
           ( 'A4444-EXT4',    NULL ),
           ( 'A5555-55555',   '555555' );
    
    WITH Query AS
    (
    SELECT Belegnummer,
           Bestellnummer,
           CHARINDEX( '-', Belegnummer ) AS x,
           SUBSTRING( Belegnummer, 1, CHARINDEX( '-', Belegnummer ) - 1 ) AS ExtrahierteBelegnummer,
           SUBSTRING( Belegnummer, CHARINDEX( '-', Belegnummer ) + 1, LEN( Belegnummer ) - CHARINDEX( '-', Belegnummer ) + 1 ) AS ExtrahierteBestellnummer
    FROM @Belege 
    )
    SELECT *
    FROM   Query


    Gruß, Stefan
    Microsoft MVP - Visual Developer ASP/ASP.NET (2001-2018)
    https://www.asp-solutions.de/ - IT Beratung, Softwareentwicklung, Remotesupport

    Donnerstag, 14. Oktober 2021 10:34
    Moderator
  • Du kannst per Replace einen Text auch durch eine leere Zeichenkette ersetzen:

    replace(Text, Belegnr, '')

    Damit wird, falls die Belegnummer im Text vorkommt, diese entfernt.

    Donnerstag, 14. Oktober 2021 11:17
  • Wenn ich es richtig verstanden habe, ist der letzte Bindestrich der Trenner, da die Fremdbelegungsnummer vorne steht.

    Zeile 2 und 3 im Beispiel bilden das jetzt ab.

    HTH!

    DECLARE @Belege TABLE( Belegnummer nvarchar( 255 ), Bestellnummer nvarchar( 255 ) );
    
    INSERT INTO @Belege( Belegnummer, Bestellnummer )
    VALUES ( 'A1111-123456',  '123456' ),
           ( 'A2222-EXT222-22', NULL ),
           ( 'A33-33-567890',  '567890' ),
           ( 'A4444-EXT4',    NULL ),
           ( 'A5555-54321',   '54321' );
    
    WITH Query AS
    (
    SELECT Belegnummer,
           Bestellnummer,
            case 
    			when Bestellnummer is null then null
    			else Len(Belegnummer) - CHARINDEX( '-', reverse(Belegnummer) ) + 1 
    		end AS last_delimiter_position,
    		case 
    			when Bestellnummer is null then Belegnummer
    			else SUBSTRING( Belegnummer, 1, (Len(Belegnummer) - CHARINDEX( '-', reverse(Belegnummer) ) + 1) - 1  ) 
    		end AS ExtrahierteBelegnummer,
           case 
    			when Bestellnummer is null then null
    			else SUBSTRING( Belegnummer, Len(Belegnummer) - CHARINDEX( '-', reverse(Belegnummer) ) + 2 , LEN( Belegnummer ) - (Len(Belegnummer) - CHARINDEX( '-', reverse(Belegnummer) ) + 1) + 1 ) 
    		end AS ExtrahierteBestellnummer
    FROM @Belege 
    )
    SELECT *
    FROM  Query;


    Einen schönen Tag noch, Christoph - http://www.insidesql.org/blogs/cmu

    Freitag, 15. Oktober 2021 07:29
    Beantworter

Alle Antworten

  • Mit der String-Funktion right kannst Du den rechten Teil eines Strings unter Angabe der Zeichenanzahl extrahieren. Anleitung unter https://docs.microsoft.com/de-de/sql/t-sql/functions/right-transact-sql?view=sql-server-ver15

    Da die Länge der Fremdbestellnummer nicht fix, kannst Du mit der Funktion len und der Anzahl der fixen Belegnummer die Zeichenanzahl der Fremdbestellnummer bestimmen. 



    Donnerstag, 14. Oktober 2021 10:17
  • Hallo, 

    wie Stefan angemerkt hat: Der große Vorteil in der SQL-Gruppe ist das Du mittels Create Table und insert into Statements deine Datenbanktabelle komplett beschreiben kannst. Das erleichtert die Sache erheblich.

    Schau mal ob das hier trifft:

    --Bsp.: Belegnummer: 6644619-21001129   Bestellnummer: 21001129
    --Bsp.: Belegnummer: 1234567-0185.FL Bestellnummer: isNull

    select * from
    (
    select '6644619-21001129' as belegnummer , '21001129' as bestellnummer
    union
    select '1234567-0185.FL' as belegnummer , null as bestellnummer
    union
    select null as belegnummer , null as bestellnummer
    union 
    select '6644619-21001128' as belegnummer , '21001128a' as bestellnummer
    ) x
    where
    CHARINDEX(x.bestellnummer,x.belegnummer)>0

     HTH
    Grüße Alexander

    Donnerstag, 14. Oktober 2021 10:24
  • Hi,

    auch hier (wieder) der Hinweis, dass es immer sinnvoll und für die Antwortenden (und daher letztendlich auch für dich selbst) sehr hilfreich ist, wenn Du die Tabellendefinition als CREATE TABLE oder DECLARE @... TABLE Statements, Beispieldaten als INSERT INTO Statements und die gewünschten Ergebnisse, exakt auf den genannten Beispieldaten basierend, posten würdest. Bitte zukünftig dran denken!

    In deinem Fall ist der Bindestrich doch der Trenner. Das wäre doch dein Zeichen, an dem Du dich orientieren kannst.

    DECLARE @Belege TABLE( Belegnummer nvarchar( 255 ), Bestellnummer nvarchar( 255 ) );
    
    INSERT INTO @Belege( Belegnummer, Bestellnummer )
    VALUES ( 'A1111-111111',  '111111' ),
           ( 'A2222-EXT22222', NULL ),
           ( 'A3333-333333',  '333333' ),
           ( 'A4444-EXT4',    NULL ),
           ( 'A5555-55555',   '555555' );
    
    WITH Query AS
    (
    SELECT Belegnummer,
           Bestellnummer,
           CHARINDEX( '-', Belegnummer ) AS x,
           SUBSTRING( Belegnummer, 1, CHARINDEX( '-', Belegnummer ) - 1 ) AS ExtrahierteBelegnummer,
           SUBSTRING( Belegnummer, CHARINDEX( '-', Belegnummer ) + 1, LEN( Belegnummer ) - CHARINDEX( '-', Belegnummer ) + 1 ) AS ExtrahierteBestellnummer
    FROM @Belege 
    )
    SELECT *
    FROM   Query


    Gruß, Stefan
    Microsoft MVP - Visual Developer ASP/ASP.NET (2001-2018)
    https://www.asp-solutions.de/ - IT Beratung, Softwareentwicklung, Remotesupport

    Donnerstag, 14. Oktober 2021 10:34
    Moderator
  • Du kannst per Replace einen Text auch durch eine leere Zeichenkette ersetzen:

    replace(Text, Belegnr, '')

    Damit wird, falls die Belegnummer im Text vorkommt, diese entfernt.

    Donnerstag, 14. Oktober 2021 11:17
  • Hallo zusammen und vielen Dank für die Antworten, die Hilfe und die Lösungsansätze.
    Für mich als Controller war dann am Ende die Syntax von "der Suchende" oder über die Längenermittlung am einfachsten, da es schon ein ziemlich umfangreiches SQL war:

    CASE WHEN BestellNr > 0 THEN REPLACE(FremdbelegNr, '-' + BestellNr, '') ELSE FremdbelegNr

    Erstelle die SQL i.d.R. über den Grafikassistenten und komme meist mit left/right/substring case when gut durch Leben, daher nochmals um Nachsicht. Alle Ansätze sind Klasse, aber unser DWH hat so seine Tücken!

    Stefan hat natürlich recht, versuche beim nächsten mal ein konkretes SQL-Beispiel. Mit der Orientierung am '-' kann es schwer werden, da die Fremdbelegsnummer '-' mehrere haben könnte. Der Fall, dass Fremdbeleg = Bestellnummer ist schließe ich mal nahezu aus.... Das ganze soll die manuelle Rechnungsprüfung mit Papier zur Rechnungskontrolle minimieren, da hilft sowas schon ungemein auch wenn ich nicht 100% erwische sollte es 99,9% sein :-) ..... 
    Donnerstag, 14. Oktober 2021 13:13
  • Dadurch dass du das "-" voranstellst, wird ja nur der 2. Teil des Textes ersetzt.
    Den case kannst du dir noch sparen, denn wenn nichts gefunden wird, du suchst ja dann "-0", kann auch nichts ersetzt werden.

    Donnerstag, 14. Oktober 2021 13:22
  • Mit der Orientierung am '-' kann es schwer werden, da die Fremdbelegsnummer '-' mehrere haben könnte.

    Du brauchst ja nur den ersten Bindestrich. Das ist ja der Trenner. Die weiteren musst Du ja nicht beachten.


    Gruß, Stefan
    Microsoft MVP - Visual Developer ASP/ASP.NET (2001-2018)
    https://www.asp-solutions.de/ - IT Beratung, Softwareentwicklung, Remotesupport

    Donnerstag, 14. Oktober 2021 14:13
    Moderator
  • Wenn ich es richtig verstanden habe, ist der letzte Bindestrich der Trenner, da die Fremdbelegungsnummer vorne steht.

    Zeile 2 und 3 im Beispiel bilden das jetzt ab.

    HTH!

    DECLARE @Belege TABLE( Belegnummer nvarchar( 255 ), Bestellnummer nvarchar( 255 ) );
    
    INSERT INTO @Belege( Belegnummer, Bestellnummer )
    VALUES ( 'A1111-123456',  '123456' ),
           ( 'A2222-EXT222-22', NULL ),
           ( 'A33-33-567890',  '567890' ),
           ( 'A4444-EXT4',    NULL ),
           ( 'A5555-54321',   '54321' );
    
    WITH Query AS
    (
    SELECT Belegnummer,
           Bestellnummer,
            case 
    			when Bestellnummer is null then null
    			else Len(Belegnummer) - CHARINDEX( '-', reverse(Belegnummer) ) + 1 
    		end AS last_delimiter_position,
    		case 
    			when Bestellnummer is null then Belegnummer
    			else SUBSTRING( Belegnummer, 1, (Len(Belegnummer) - CHARINDEX( '-', reverse(Belegnummer) ) + 1) - 1  ) 
    		end AS ExtrahierteBelegnummer,
           case 
    			when Bestellnummer is null then null
    			else SUBSTRING( Belegnummer, Len(Belegnummer) - CHARINDEX( '-', reverse(Belegnummer) ) + 2 , LEN( Belegnummer ) - (Len(Belegnummer) - CHARINDEX( '-', reverse(Belegnummer) ) + 1) + 1 ) 
    		end AS ExtrahierteBestellnummer
    FROM @Belege 
    )
    SELECT *
    FROM  Query;


    Einen schönen Tag noch, Christoph - http://www.insidesql.org/blogs/cmu

    Freitag, 15. Oktober 2021 07:29
    Beantworter
  • Hier gibts auch ein Bespiel:
    https://stackoverflow.com/questions/1024978/find-index-of-last-occurrence-of-a-sub-string-using-t-sql/20369450

    reverse(left(reverse(feld), charindex('-', reverse(feld)) -1)
    Den hier extrahierten Wert kannst du dann mit deiner Beleg-Nr. vergleichen und dann per Replace entfernen. Den "-1" kannst du dir schenken, wenn das "-" auch benötigt/entfernt werden soll.
    Allerdings muss man auch per charindex prüfen, ob überhaupt ein "-" vorkommt, sonst gibts einen Fehler.

    Freitag, 15. Oktober 2021 08:51