Replace von CHAR(0) in SQL Server varchar-Spalte

Heute kam es in einem meiner Projekt zu folgendem Fehler

Msg 6841, Level 16, State 1, Line 1
FOR XML konnte die Daten für den ‘Tabelle’-Knoten nicht serialisieren, weil ein in XML unzulässiges Zeichen (0x0000) enthalten ist. Um diese Daten mithilfe von FOR XML abzurufen, konvertieren Sie sie in den Datentyp ‘binary’, ‘varbinary’ oder ‘image’, und verwenden Sie die BINARY BASE64-Direktive.

Nach kurzem Suchen bemerkte ich dann, dass nicht die Ausgabe mittels FOR XML fehlerhaft war, sondern in einzelnen Feldern einer Tabelle CHAR(0)-Zeichen enthalten waren, die da nicht hingehörten. Die Tabelle wird von anderer Stelle gefüllt und eigentlich wird dabei der entsprechende Text abgeschnitten, dieses Sonderzeichens in dem Fall wohl aber nicht mehr. Nachdem die Befüllung geändert wurde, mussten die bereits geschriebenen CHAR(0)-Einträge noch geändert werden, doch das stellte sich als schwerer heraus als zuerst angenommen. Sicher war, dass die Zeichen nur am Ende eines Textes vorkamen. Zum Testen gab es folgende temporäre Tabelle

DECLARE @test table(
    Name varchar(30) NULL COLLATE Latin1_General_CI_AS
)
INSERT INTO @test (Name)
            SELECT 'Hallo Welt 1' + CHAR(0) + CHAR(0) + CHAR(0) + CHAR(0)
UNION ALL   SELECT 'Hallo Welt 2' + CHAR(0)
UNION ALL   SELECT 'Hallo Welt 3'
UNION ALL   SELECT ''
UNION ALL   SELECT CHAR(0)
UNION ALL   SELECT CHAR(0) + CHAR(0)
UNION ALL   SELECT NULL

Das Problem sollte schnell gelöst werden können.

1
2
3

UPDATE  @test
SET     Name = SUBSTRING(Name, 1, CHARINDEX(CHAR(0), Name) - 1)
WHERE   Name LIKE '%' + CHAR(0) + '%'

Sieht einfach aus, macht - zumindest theoretisch - genau dass, was ich will, funktioniert aber nicht :(

Führt man die Abfrage aus, werden nur 2 Datensätze angepasst - und das kann ja nicht sein, es werden definitiv 4 Zeilen mit CHAR(0) gefüllt. Die WHERE-Klausel muss also fehlerhaft sein. Ich dachte mir, dass LIKE eventuell so seine Probleme mit CHAR(0) hat und stellte die Abfrage um.

1
2
3

UPDATE @test
SET    Name = SUBSTRING(Name, 1, CHARINDEX(CHAR(0), Name) - 1)
WHERE  CHARINDEX(CHAR(0), Name) > 0

Doch auch diese Abfrage führte zur Änderung von lediglich zwei Datensätze - um genau zu sein, sind es die Datensätze, bei denen CHAR(0) an erster Stelle steht. Folglich muss es aber so sein, dass auch SUBSTRING nicht funktionieren kann, selbst wenn die WHERE-Klausel korrekt wäre. Nun - so dachte ich - dann ersetze ich eben alle CHAR(0) durch Leerzeichen und schneide in einem zweiten Schritt diese dann mit TRIMLEFT ab.

Doch auch das funktioniert einfach nicht. REPLACE findet nur CHAR(0) in den beiden Zeilen, die auch mit CHAR(0) anfangen. Beim Ersetzen wird aber nur das erste Vorkommen davon ersetzt, der Rest wird abgeschnitten. Nach

1 2	UPDATE @test SET Name = REPLACE(Name, CHAR(0), "X")

wird aus CHAR(0) ebenso ‘X’ wie auch aus CHAR(0) + CHAR(0)… Hier wird ein CHAR(0) offensichtlich dazu verwendet, den Text als beendet zu kennzeichnen.

Warum es so ist wie es ist kann ich leider immer noch nicht sagen. Wenn man aber als COLLATION statt Latin1_General_CI_AS eine SQL-Server interne Sortierung wie z.B. SQL_Latin1_General_CP1_CI_AS verwendet (grob gilt, dass alle Sortiernamen mit dem Prefix “SQL_” SQL-Sortierungen und alle anderen Windows-Sortierungen sind), dann funktioniert alle bisher beschriebenen Abfragen. Wenn man diese aber nicht unbedingt in der Tabelle direkt Umstellen kann oder will, geht es auch inline. So liefert z.B.

1 2	UPDATE @test SET Name = REPLACE(Name COLLATE SQL_Latin1_General_CP1_CI_AS, CHAR(0), "X")

ebenso das zuvor gewünschte Zwischenergebnis wie

1
2
3

UPDATE  @test
SET     Name = SUBSTRING(Name, 1, CHARINDEX(CHAR(0), Name COLLATE SQL_Latin1_General_CP1_CI_AS) - 1)
WHERE   Name COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%' + CHAR(0) + '%'

das eigentlich gewünschte Endergebnis… Man darf es nur an keiner Stelle vergessen, bei der nach Sonderzeichen gesucht wird. Im ersten Parameter von SUBSTRING ist es dagegen einfach nicht notwendig.