meierjo
Goto Top

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

Content-Key: 33929380784

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

Printed on: July 14, 2024 at 16:07 o'clock

Member: Cleanairs
Cleanairs Sep 28, 2023 at 07:28:27 (UTC)
Goto Top
Versuch mal:

SELECT
  TRIM(REPLACE(Header, '<FONT color=#ff0080>', ''))  
FROM
  [Tabelle]
Mitglied: 7907292512
7907292512 Sep 28, 2023 updated at 08:18:40 (UTC)
Goto Top
Member: Meierjo
Meierjo Sep 28, 2023 at 08:29:50 (UTC)
Goto Top
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
Member: Meierjo
Meierjo Sep 28, 2023 at 08:33:43 (UTC)
Goto Top
Hallo


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
Mitglied: 7907292512
7907292512 Sep 28, 2023 updated at 08:38:22 (UTC)
Goto Top
Thread genau lesen bitte, Replace() arbeitet nicht mit Regex.
Member: Meierjo
Meierjo Sep 28, 2023 at 08:49:34 (UTC)
Goto Top
Hallo

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

Gruss
Member: em-pie
em-pie Sep 28, 2023 updated at 09:12:41 (UTC)
Goto Top
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
---------------------------------
1: 2210565
2: 2210024
3: 2210831
4: 2210818 Komm:2588224
5: Gem:Angebot Nr.xxx yyyy
6: NULL
7: 2210383
Member: Meierjo
Meierjo Sep 28, 2023 at 09:27:14 (UTC)
Goto Top
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
Member: Cleanairs
Solution Cleanairs Sep 28, 2023 at 10:01:30 (UTC)
Goto Top
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?
Member: Meierjo
Meierjo Sep 28, 2023 at 11:07:14 (UTC)
Goto Top
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 <b> übrig, aber das bringe ich selber noch weg, wenn ich deinen Code verstanden habe

Danke
Member: Cleanairs
Cleanairs Sep 28, 2023 at 11:09:32 (UTC)
Goto Top
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;
Member: Meierjo
Meierjo Sep 28, 2023 at 11:20:44 (UTC)
Goto Top
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
Member: Cleanairs
Cleanairs Sep 28, 2023 at 11:26:57 (UTC)
Goto Top
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.
Mitglied: 7907292512
7907292512 Sep 28, 2023 updated at 11:42:38 (UTC)
Goto Top
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.
Member: Meierjo
Meierjo Sep 28, 2023 at 11:50:39 (UTC)
Goto Top
Hy

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</FONT>
2210024</FONT>
2210831</FONT>
2210818 Komm:2588224</FONT>
Gem:Angebot Nr.xxx YYYY</FONT>
2210383</FONT>

Gruss
Member: Cleanairs
Cleanairs Sep 28, 2023 at 11:56:49 (UTC)
Goto Top
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
Member: Meierjo
Meierjo Sep 28, 2023 at 11:57:53 (UTC)
Goto Top
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;
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.
Die Funktion REPLACE_REGEXP ist nicht bekannt

Gruss
select replace
Mitglied: 7907292512
7907292512 Sep 28, 2023 updated at 12:01:32 (UTC)
Goto Top
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.
Member: Meierjo
Meierjo Sep 28, 2023 at 12:01:59 (UTC)
Goto Top
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>
Member: Meierjo
Meierjo Sep 28, 2023 at 12:04:45 (UTC)
Goto Top
Zitat von @7907292512:

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.

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
Mitglied: 7907292512
Solution 7907292512 Sep 28, 2023 updated at 12:31:55 (UTC)
Goto Top
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.
Member: ukulele-7
Solution ukulele-7 Sep 28, 2023 at 12:22:38 (UTC)
Goto Top
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. <FONT color=#ff0080> existiert wird das ersetzt und damit ist die CASE Anweisung abgeschlossen. Auf <FONT size=6> 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 ...
Member: Meierjo
Meierjo Sep 28, 2023 at 13:51:35 (UTC)
Goto Top
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
Member: em-pie
em-pie Sep 28, 2023 at 14:09:40 (UTC)
Goto Top
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...
Member: ukulele-7
ukulele-7 Sep 28, 2023 at 14:16:46 (UTC)
Goto Top
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.