SQL Server: Ersetzen Sie eine Liste der ASCII-Zeichen aus einer VARCHAR(MAX) - Feld
Ich habe eine Tabelle mit rund 4 Millionen Datensätze und ich muss entfernen Sie eine Instanz der ASCII-Zeichen im folgenden Bereich(en) in einem VARCHAR(MAX)
Spalte.
- 00 - 08
- 11 - 12
- 14 - 31
- 127
Bitte beachten Sie den oben genannten stellen die dezimalen Kennungen der einzelnen Zeichen, den eigentlichen ASCII-Zeichen ersetzt werden können, werden gegengelesen gegen http://www.asciitable.com/.
Ich habe versucht, die folgenden Arten und die gewünschten Ergebnisse erzielt.
Erste Möglichkeit: Verwendung von nested REPLACE
Funktionen:
UPDATE [MyTable]
SET replace_ascii_chars = REPLACE(REPLACE(replace_ascii_chars, char(0), '')
,char(1), '')....
Zweite Möglichkeit: erstellt die folgende SQL-Funktion auf und lief ein UPDATE
Aussage:
CREATE FUNCTION [dbo].RemoveASCIICharactersInRange(@InputString VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
IF @InputString IS NOT NULL
BEGIN
DECLARE @Counter INT, @TestString NVARCHAR(40)
SET @TestString = '%[' + NCHAR(0) + NCHAR(1) + NCHAR(2) + NCHAR(3) + NCHAR(4) + NCHAR(5) + NCHAR(6) + NCHAR(7) + NCHAR(8) + NCHAR(11) + NCHAR(12) + NCHAR(14) + NCHAR(15) + NCHAR(16) + NCHAR(17) + NCHAR(18) + NCHAR(19) + NCHAR(20) + NCHAR(21) + NCHAR(22) + NCHAR(23) + NCHAR(24) + NCHAR(25) + NCHAR(26) + NCHAR(27) + NCHAR(28) + NCHAR(29) + NCHAR(30) + NCHAR(31) + NCHAR(127)+ ']%'
SELECT @Counter = PATINDEX (@TestString, @InputString COLLATE Latin1_General_BIN)
WHILE @Counter <> 0
BEGIN
SELECT @InputString = STUFF(@InputString, @Counter, 1, '')
SELECT @Counter = PATINDEX (@TestString, @InputString COLLATE Latin1_General_BIN)
END
END
RETURN(@InputString)
END
UPDATE SQL:
UPDATE [MyTable]
SET replace_ascii_chars = [dbo].RemoveASCIICharactersInRange(replace_ascii_chars)
Beide der genannten Möglichkeiten geklappt hat, aber scheint ein bisschen langsam (etwa 1,5 Stunden). Ist dort Weg, es zu beschleunigen, die in SQL Server? Ich will nicht zu tun, die Ersetzungen in der Anwendungsschicht, wie die Werte sind bereits gespeichert, die in dieser Art und Weise.
Auch nicht alle 4 Millionen Datensätze haben nicht diese Zeichen, so gibt es eine Möglichkeit es zu beschleunigen?
BEARBEITEN:
Ich war falsch zu sagen, WO-Klausel, die länger (es war langsamer, weil der Art, wie ich verwendet, die where-Klausel früher). Ich habe aktualisiert die where-Klausel als pro @MWillemse Vorschlag, und es scheint zu sein, sehr schnell jetzt.
Denn es ist in der Datenbank nicht bedeutet, die Datenbank ist das beste Werkzeug für die Aufgabe. Ich würde dies in einer Anwendung wie .NET. Oder Schreibe einen CLR, um es zu tun.
Haben Sie versucht, dieses:
WHERE data LIKE '%['+CHAR(0)+','+CHAR(1)+','+CHAR(2)+','+CHAR(3)+','+CHAR(4)+']%'
Danke @MWillemse für Ihren Vorschlag. Danke jarlh, Balsam für Ihre Kommentare.
InformationsquelleAutor Sathish | 2015-02-17
Du musst angemeldet sein, um einen Kommentar abzugeben.
Die Abfrage läuft nun schneller, nachdem ich eine WHERE-Klausel Hinzugefügt, die als pro @MWillemse Vorschlag.
Danke @MWillemse, @jarlh, @Balsam für Ihre wertvollen Kommentare.
EDIT: Bitte beachten Sie, dieser würde nur von
SQL SERVER 2012
und oben. Bitte Folgen Sie diesem stackoverflow link für die Diskussion über Probleme mit der LOB-Daten-Typen und bestimmte Sortierungen verwendet werden, wenn mitREPLACE
Funktion bei älterenSQL Server
Versionen.InformationsquelleAutor Sathish