28.09.2023, aktualisiert um 13:11:55 Uhr

3403

SQL-Abfrage Tags oder Steuerzeichen entfernen

Hallo

Ich bin Anfänger in Sachen SQL-Abfragen, und habe folgendes Problem:
In einer SQL-Tabelle (SAP) stehen im Feld Header zb folgende Inhalte

<FONT color=#ff0080>2210565</FONT>
<FONT size=6>2210024</FONT>
<FONT size=6><b>2210831</b></FONT>
<FONT size=6><b>2210818 Komm:2588224</b></FONT>
<FONT size=6><b>Gem:Angebot Nr.xxx yyyy</b></FONT>
NULL
<FONT size=6><b>2210383</b></FONT>

Nun möchte ich nur den reinen Text ohne "Steuerzeichen in einem SQL-Select haben

Also
2210565
2210024
2210831
2210818 Komm:2588224
Gem:Angebot Nr.xxx yyyy
NULL
2210383

Kann mir bitte jemand auf die Sprünge helfen, danke

Gruss

Bitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben

Content-ID: 33929380784

Url: https://administrator.de/contentid/33929380784

Ausgedruckt am: 24.11.2024 um 05:11 Uhr

25 Kommentare

Neuester Kommentar

Versuch mal:

SELECT
  TRIM(REPLACE(Header, '<FONT color=#ff0080>', ''))  
FROM
  [Tabelle]

Mit nem Regex die Tags entfernen
https://tio.run/##K8gvTy0qzkjNyfn/30GJy8bN3y9EITk/J7/IVjktzcDAwsDOyMjQwN ...
</?[^>]+>

https://stackoverflow.com/questions/21378193/regex-pattern-inside-sql-re ...

Gruß sid

Hallo

SELECT
  TRIM(REPLACE(Header, '<FONT color=#ff0080>', ''))  
FROM
  [Tabelle]

Folgende Fehlermeldung kommt dabei raus
Meldung 8116, Ebene 16, Status 1, Zeile 1
Argument data type ntext is invalid for argument 1 of replace function.

Gruss

Hallo

https://tio.run/##K8gvTy0qzkjNyfn/30GJy8bN3y9EITk/J7/IVjktzcDAwsDOyMjQwN ...
</?[^>]+>

https://stackoverflow.com/questions/21378193/regex-pattern-inside-sql-re ...

Wenn ich das so

select Tab1, Tab2, REPLACE(Tab3, '</?[^>]+>', ''), * from dbo.YYYY

eintrage, erscheint dieselbe Fehlermeldung
Meldung 8116, Ebene 16, Status 1, Zeile 1
Argument data type ntext is invalid for argument 1 of replace function.

Gruss

Thread genau lesen bitte, Replace() arbeitet nicht mit Regex.

Hallo

Sorry, aber das auf der Site Stackoverflow versteh ich nicht, ist mir zu hoch

Gruss

Moin,

Wenn das eine wiederkehrende Geschichte ist (wovon ich ausgehe):
Baue dir eine Funktion, in der du aus HTML Plaintext erstellst. Z. B.
https://www.concordbusinessservicesllc.com/posts/sql-server-html-to-plai ...

Wobei dein Ergebnis nicht eindeutig ist. Willst du dann nach der Konvertierung alles in einem Datenfeld haben oder soll jede HTML-Zeile ein einzelner Datensatz werden?

   Content
---------------------------------
1: 2210565
   2210024
   2210831
   2210818 Komm:2588224
   Gem:Angebot Nr.xxx yyyy
   NULL
   2210383

vs.

   Content
---------------------------------
2210565
2210024
2210831
2210818 Komm:2588224
Gem:Angebot Nr.xxx yyyy
NULL
2210383

Hallo

Wobei dein Ergebnis nicht eindeutig ist. Willst du dann nach der Konvertierung alles in einem Datenfeld haben oder soll jede HTML-Zeile ein einzelner Datensatz werden?

Es soll jede HTML-Zeile ein einzelner Datensatz werden
Ich habe die 7 Zeilen nur aufgeführt, weil ich die verschiedenen möglichen Inhalte zeigen wollte (nur Zahlenfolge / Leerzeichen / Sonderzeichen :., etc / NULL

Gruss

SELECT
  CASE
    WHEN CHARINDEX('<FONT color=#ff0080>', Header) > 0 THEN  
      SUBSTRING(Header, CHARINDEX('<FONT color=#ff0080>', Header) + LEN('<FONT color=#ff0080>'), CHARINDEX('</FONT>', Header) - CHARINDEX('<FONT color=#ff0080>', Header) - LEN('<FONT color=#ff0080>'))  
    WHEN CHARINDEX('<FONT size=6>', Header) > 0 THEN  
      SUBSTRING(Header, CHARINDEX('<FONT size=6>', Header) + LEN('<FONT size=6>'), CHARINDEX('</FONT>', Header) - CHARINDEX('<FONT size=6>', Header) - LEN('<FONT size=6>'))  
    ELSE Header
  END AS CleanedHeaderText
FROM DeineTabelle;

Geht das?

Hallo

SELECT
  CASE
    WHEN CHARINDEX('<FONT color=#ff0080>', Header) > 0 THEN  
      SUBSTRING(Header, CHARINDEX('<FONT color=#ff0080>', Header) + LEN('<FONT color=#ff0080>'), CHARINDEX('</FONT>', Header) - CHARINDEX('<FONT color=#ff0080>', Header) - LEN('<FONT color=#ff0080>'))  
    WHEN CHARINDEX('<FONT size=6>', Header) > 0 THEN  
      SUBSTRING(Header, CHARINDEX('<FONT size=6>', Header) + LEN('<FONT size=6>'), CHARINDEX('</FONT>', Header) - CHARINDEX('<FONT size=6>', Header) - LEN('<FONT size=6>'))  
    ELSE Header
  END AS CleanedHeaderText
FROM DeineTabelle;

Geht das?

Teilweise ja, es bleibt noch das übrig, aber das bringe ich selber noch weg, wenn ich deinen Code verstanden habe

Danke

Top.

Dann das als Update hinterher:

SELECT
  CASE
    WHEN CHARINDEX('<FONT color=#ff0080>', Header) > 0 THEN    
      SUBSTRING(Header, CHARINDEX('<FONT color=#ff0080>', Header) + LEN('<FONT color=#ff0080>'), CHARINDEX('</FONT>', Header) - CHARINDEX('<FONT color=#ff0080>', Header) - LEN('<FONT color=#ff0080>'))    
    WHEN CHARINDEX('<FONT size=6>', Header) > 0 THEN    
      SUBSTRING(Header, CHARINDEX('<FONT size=6>', Header) + LEN('<FONT size=6>'), CHARINDEX('</FONT>', Header) - CHARINDEX('<FONT size=6>', Header) - LEN('<FONT size=6>'))    
    WHEN CHARINDEX('<b>', Header) > 0 THEN    
      REPLACE(REPLACE(Header, '<b>', ''), '</b>', '')  
    ELSE Header
  END AS CleanedHeaderText
FROM DeineTabelle;

Hallo

Dann das als Update hinterher:

SELECT
  CASE
    WHEN CHARINDEX('<FONT color=#ff0080>', Header) > 0 THEN    
      SUBSTRING(Header, CHARINDEX('<FONT color=#ff0080>', Header) + LEN('<FONT color=#ff0080>'), CHARINDEX('</FONT>', Header) - CHARINDEX('<FONT color=#ff0080>', Header) - LEN('<FONT color=#ff0080>'))    
    WHEN CHARINDEX('<FONT size=6>', Header) > 0 THEN    
      SUBSTRING(Header, CHARINDEX('<FONT size=6>', Header) + LEN('<FONT size=6>'), CHARINDEX('</FONT>', Header) - CHARINDEX('<FONT size=6>', Header) - LEN('<FONT size=6>'))    
    WHEN CHARINDEX('<b>', Header) > 0 THEN    
      REPLACE(REPLACE(Header, '<b>', ''), '</b>', '')  
    ELSE Header
  END AS CleanedHeaderText
FROM DeineTabelle;

Dann kommt wieder die Fehlermeldung
Argument data type ntext is invalid for argument 1 of replace function.

Gruss

Es scheint, dass du mit einer Datenbank arbeitest, in der der Datentyp der Spalte "Header" ntext ist, und die REPLACE-Funktion nicht direkt auf ntext-Daten angewendet werden kann.

In diesem Fall kannst du die CAST-Funktion verwenden, um den ntext in einen geeigneten Textdatentyp (z. B. nvarchar(max)) umzuwandeln, die REPLACE-Funktion auszuführen und dann das Ergebnis wieder in ntext umzuwandeln. Hier ist eine modifizierte Version des SQL-Statements:

SELECT
  CASE
    WHEN CHARINDEX('<FONT color=#ff0080>', Header) > 0 THEN    
      CAST(REPLACE(CAST(Header AS nvarchar(max)), '<FONT color=#ff0080>', '') AS ntext)  
    WHEN CHARINDEX('<FONT size=6>', Header) > 0 THEN    
      CAST(REPLACE(CAST(Header AS nvarchar(max)), '<FONT size=6>', '') AS ntext)  
    WHEN CHARINDEX('<b>', Header) > 0 THEN    
      CAST(REPLACE(REPLACE(CAST(Header AS nvarchar(max)), '<b>', ''), '</b>', '') AS ntext)  
    ELSE Header
  END AS CleanedHeaderText
FROM DeineTabelle;

Dies sollte die Fehlermeldung beheben und die gewünschten Ergebnisse liefern.

SAP Hana kennt laut Doku auch eine REGEX Replace Funktion in deren SQL-Syntax, das hier sollte IMHO wohl auch funktionieren ohne das man die Strings fest in den Select einbauen müsste:

SELECT 
    REPLACE_REGEXPR('<[^>]+>' IN DeineSpalte WITH '' OCCURRENCE ALL) AS DeineSpalteCleaned   
FROM DeineTabelle;

https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f675 ...

Leider wird die genutzte Abfrage-Platform vom TO nicht genannt damit man passende effektivere SQL Syntax liefern kann.

Sid.

Zitat von @Cleanairs:

Es scheint, dass du mit einer Datenbank arbeitest, in der der Datentyp der Spalte "Header" ntext ist, und die REPLACE-Funktion nicht direkt auf ntext-Daten angewendet werden kann.

In diesem Fall kannst du die CAST-Funktion verwenden, um den ntext in einen geeigneten Textdatentyp (z. B. nvarchar(max)) umzuwandeln, die REPLACE-Funktion auszuführen und dann das Ergebnis wieder in ntext umzuwandeln. Hier ist eine modifizierte Version des SQL-Statements:

SELECT
  CASE
    WHEN CHARINDEX('<FONT color=#ff0080>', Header) > 0 THEN    
      CAST(REPLACE(CAST(Header AS nvarchar(max)), '<FONT color=#ff0080>', '') AS ntext)  
    WHEN CHARINDEX('<FONT size=6>', Header) > 0 THEN    
      CAST(REPLACE(CAST(Header AS nvarchar(max)), '<FONT size=6>', '') AS ntext)  
    WHEN CHARINDEX('<b>', Header) > 0 THEN    
      CAST(REPLACE(REPLACE(CAST(Header AS nvarchar(max)), '<b>', ''), '</b>', '') AS ntext)  
    ELSE Header
  END AS CleanedHeaderText
FROM DeineTabelle;

Dies sollte die Fehlermeldung beheben und die gewünschten Ergebnisse liefern.

Leider nicht, nun sieht das Ergebnis folgendermassen aus

2210565
2210024
2210831
2210818 Komm:2588224
Gem:Angebot Nr.xxx YYYY
2210383

Gruss

Wie schauts hiermit aus?

SELECT 
  CASE 
    WHEN CHARINDEX('<FONT color=#ff0080>', Header) > 0 THEN    
      SUBSTRING(Header, CHARINDEX('<FONT color=#ff0080>', Header) + LEN('<FONT color=#ff0080>'), CHARINDEX('</FONT>', Header, CHARINDEX('<FONT color=#ff0080>', Header)) - CHARINDEX('<FONT color=#ff0080>', Header) - LEN('<FONT color=#ff0080>'))    
    WHEN CHARINDEX('<FONT size=6>', Header) > 0 THEN    
      SUBSTRING(Header, CHARINDEX('<FONT size=6>', Header) + LEN('<FONT size=6>'), CHARINDEX('</FONT>', Header, CHARINDEX('<FONT size=6>', Header)) - CHARINDEX('<FONT size=6>', Header) - LEN('<FONT size=6>'))    
    WHEN CHARINDEX('<b>', Header) > 0 THEN    
      SUBSTRING(Header, CHARINDEX('<b>', Header) + LEN('<b>'), CHARINDEX('</b>', Header, CHARINDEX('<b>', Header)) - CHARINDEX('<b>', Header) - LEN('<b>'))    
    ELSE Header
  END AS CleanedHeaderText
FROM DeineTabelle;

ist halt manchmal etwas Rate Mal mit Rosenthal hier

Hallo

Zitat von @7907292512:

SAP Hana kennt laut Doku auch eine REGEX Replace Funktion in deren SQL-Syntax, das hier sollte IMHO wohl auch funktionieren ohne das man die Strings fest in den Select einbauen müsste:

SELECT 
    REPLACE_REGEXPR('<[^>]+>' IN DeineSpalte WITH '' OCCURRENCE ALL) AS DeineSpalteCleaned   
FROM DeineTabelle;

Die Funktion REPLACE_REGEXP ist nicht bekannt

Gruss

Die Funktion REPLACE_REGEXP ist nicht bekannt

Ja, und genau deswegen habe ich nach der der genutzten Platform und SQL Server Software gefragt (MS SQL Server, MySQL, Oracle, usw ...) auf der du überhaupt arbeitest! Wäre ja schön wenn du das noch beantworten könntest.

Hallo

Zitat von @Cleanairs:

Wie schauts hiermit aus?

SELECT 
  CASE 
    WHEN CHARINDEX('<FONT color=#ff0080>', Header) > 0 THEN    
      SUBSTRING(Header, CHARINDEX('<FONT color=#ff0080>', Header) + LEN('<FONT color=#ff0080>'), CHARINDEX('</FONT>', Header, CHARINDEX('<FONT color=#ff0080>', Header)) - CHARINDEX('<FONT color=#ff0080>', Header) - LEN('<FONT color=#ff0080>'))    
    WHEN CHARINDEX('<FONT size=6>', Header) > 0 THEN    
      SUBSTRING(Header, CHARINDEX('<FONT size=6>', Header) + LEN('<FONT size=6>'), CHARINDEX('</FONT>', Header, CHARINDEX('<FONT size=6>', Header)) - CHARINDEX('<FONT size=6>', Header) - LEN('<FONT size=6>'))    
    WHEN CHARINDEX('<b>', Header) > 0 THEN    
      SUBSTRING(Header, CHARINDEX('<b>', Header) + LEN('<b>'), CHARINDEX('</b>', Header, CHARINDEX('<b>', Header)) - CHARINDEX('<b>', Header) - LEN('<b>'))    
    ELSE Header
  END AS CleanedHeaderText
FROM DeineTabelle;

ist halt manchmal etwas Rate Mal mit Rosenthal hier

Zeile 2210565 korrekt
Zeile 2210024 korrekt
<b>2210831</b>
<b>2210818 Komm:2588224</b>
<b>Gem:Angebot Nr.xxx YYYY</b>
NULL
<b>2210383</b>

Zitat von @7907292512:

Die Funktion REPLACE_REGEXP ist nicht bekannt

Ja sorry
Microsoft Server (ich vermute Version 2019, kann nicht nachsehen, da ich nur Zugriff auf die SQL-DB habe)
SQL Server Management Studio V18.12.1
Gruss

Zitat von @Meierjo:
Ja sorry
Microsoft Server (ich vermute Version 2019, kann nicht nachsehen, da ich nur Zugriff auf die SQL-DB habe)
SQL Server Management Studio V18.12.1
Gruss

Gut, für SQL Server würde ich mir da schnell eine CLR-Function bauen und einbinden, dann kannst du richtiges Regex nutzen, und der Select funktioniert dann auch wenn es beliebige Tags in der Spalte gibt, dann muss man auch nicht dauernd manuell Hand anlegen, wer will das schon?! Wie du das machst kannst du hier nachlesen:
Regular Expression Replace (REGEXP_REPLACE) in SQL Server

Den Code kann man sich auch schnell mit der Powershell in eine DLL kompilieren wenn man kein VS zur Hand hat.

Add-Type '  
using Microsoft.SqlServer.Server;

namespace CLRFunctions
{
    public class Regex
    {
        [SqlFunction(DataAccess = DataAccessKind.Read)]
        public static string Replace(string input, string pattern, string replacement)
        {
            if (input == null || pattern == null || replacement == null)
                return input;
            return System.Text.RegularExpressions.Regex.Replace(input, pattern, replacement);

        }
    }
}
' -OutputAssembly .\CLRFunctions.dll -ReferencedAssemblies "Microsoft.SqlServer.Server"  

In die DB einbauen wie unter dem Link beschrieben und als SQL reicht dann folgendes ...

SELECT dbo.REGEXP_REPLACE(DeineSpalte,'<[^>]+>','') as DeineSpalteCleaned FROM DeineTabelle;  

Fertsch.

Also ich finde den Ansatz sehr kurz gedacht, ist denn der Inhalt der Spalte immer gleich aufgebaut (also wirklich immer)? Oder ist da auch mal eine size=8 dabei oder eine andere Farbe? Der Ansatz hat nur einen Vorteil: Anfängerfreundlich und quick & dirty.

Außerdem ist mir eins nicht klar. Ist jetzt jede Zeile bereits ein eigenständiger Wert Header in einer eigenen Zeile oder steht der komplette HTML Inhalt in einem Feld? Bei letzterem wird das CASE nämlich nur jeweils ein THEN ausführen. Wenn z.B. existiert wird das ersetzt und damit ist die CASE Anweisung abgeschlossen. Auf wird dann gar nicht mehr geprüft, innerhalb der selben "Zelle". Um mehrere HTML Tags innerhalb einer Zelle zu entfernen bedarf es bei Nutzung von CASE einer richtigen Veraschachtelung.

Eigentlich ist für replace() das vorhanden sein einer Zeichenkette unerheblich. Die letzte Version

SELECT
  CASE
    WHEN CHARINDEX('<FONT color=#ff0080>', Header) > 0 THEN      
      CAST(REPLACE(CAST(Header AS nvarchar(max)), '<FONT color=#ff0080>', '') AS ntext)    
    WHEN CHARINDEX('<FONT size=6>', Header) > 0 THEN      
      CAST(REPLACE(CAST(Header AS nvarchar(max)), '<FONT size=6>', '') AS ntext)    
    WHEN CHARINDEX('<b>', Header) > 0 THEN      
      CAST(REPLACE(REPLACE(CAST(Header AS nvarchar(max)), '<b>', ''), '</b>', '') AS ntext)    
    ELSE Header
  END AS CleanedHeaderText
FROM DeineTabelle;

läßt sich daher auch einfacher ausdrücken:

SELECT CAST(replace(replace(replace(replace(CAST(Header AS nvarchar(max)),'<FONT color=#ff0080>',''),'<FONT size=6>',''),'<b>',''),'</FONT>','') AS ntext) AS CleanedHeaderText  
FROM DeineTabelle;

Das sollte keinen Fehler werfen und macht CASE überflüssig.

Die elegane Lösung wäre übrigens HTML zu XML konvertierten, mit XML Funktionen die gewünschten Felder auslesen und mit CROSS APPY quasi einen LEFT JOIN der Daten durchzuführen. Aber XML ist alles andere als einfach, zumindest für mich. Und gemacht habe ich sowas auch nur unter MSSQL, nie unter SAP. Aber rein vom Verständnis her hier mal zwei Beispiele:
https://dba.stackexchange.com/questions/162514/how-to-read-html-code-as- ...
https://stackoverflow.com/questions/52082220/using-sql-read-html-data-as ...

Hallo

Zitat von @ukulele-7:

Also ich finde den Ansatz sehr kurz gedacht ...

Ja, hast ja recht.
Aber ich habe keinen Einfluss, wie oder was in die SQL-Tabelle geschrieben wird.
Ich bin nur damit beschäftigt, Abfragen aus der SWL-Tabelle zu erstellen

Aber danke für deinen Input

Gruss

Aber ich habe keinen Einfluss, wie oder was in die SQL-Tabelle geschrieben wird.
Ich bin nur damit beschäftigt, Abfragen aus der SWL-Tabelle zu erstellen

Wie die Daten da rein kommen, hat @ukulele-7 ja auch nicht "krtitisiert".
Baue dir eine stabile Lösung, mit der du im MS SQL die HTML-Daten in einen Plaintext konvertieren kannst.
Und da du einen MS SQL-Server hast:
Baue dir eine Funktion, die dir aus den Daten einen Plaintext zaubert.
Die Funktion kannst du dann immer wieder in anderen Queries wiederverwenden.

 
SELECT 
  t1.*
  , [PLAINHTML] = t99.Content
FROM myTable as t1

OUTER APPLY (SELECT [dbo].udf_HTML2Plain(t1.myField)) as t99

Und wenn du dann noch die Konvertierung in den nächsten Monaten/ Jahren optimierst, greift das auch immer für alle anderen Queries, die auf die Funktion zurückgreifen...

Ja das ist wirklich eine ###arbeit. Wenn die Daten wirklich starr sind, würde ich das auch quick and dirty machen.

Konvertieren von HTML in XML scheitert auch oft weil HTML Fehler (oder Ungenauigkeiten) verzeiht, XML nicht. In diesem Fall ist z.B. das erste Problem das die Farbe nicht in Anführungsstrichen steht, das wird bei XML aber so erwartet. Am Ende baut man also wieder seinen eigenen Parser und bricht sich einen ab.

gelöst Frage Datenbanken Entwicklung

Mehr von Meierjo

Powershell: in XML-File xsd:string ersetzenMeierjo - 2 Kommentare

Excel-Vorlage über VBA aufrufenMeierjo - 10 Kommentare

Acrobat Reader und Sprungliste - JumplistMeierjo - 4 Kommentare

SBS 2011 - Fehler beim Herstellen einer Verbindung mit der Exchange-VerwaltungskonsoleMeierjo - 4 Kommentare

Heiß diskutiert