Zastąp znaki specjalne w kolumnie spacją

Próbuję napisać zapytanie, które zastępuje znaki specjalne spacją. Poniższy kod pomaga zidentyfikować wiersze. (znaki alfanumeryczne, przecinek i spacja są poprawne):

SELECT columnA
FROM tableA
WHERE columnA like '%[^a-Z0-9, ]%'

Jak mogę zintegrować funkcję replace z instrukcją select, aby wszystkie znaki inne niż alfanumeryczne, przecinki i spacje w zestawie wyników zostały zastąpione przez „” (spację). Ten nie będzie działać:

SELECT replace(columnA,'%[^a-Z0-9, ]%',' ')
FROM tableA
WHERE columnA like '%[^a-Z0-9, ]%'

Jeśli masz gwarancję, że będziesz używać tylko 26 liter alfabetu angielskiego w USA (zarówno duże, jak i małe), to na pewno możesz uniknąć używania LIKEi / lub PATINDEXprostej notacji zakresu [a-z](nie trzeba użyć wielkiej litery „Z”, gdy używa się sortowania bez rozróżniania wielkości liter).

Ale jeśli można dostać znaki nie występujące w en-us alfabet jeszcze dostępne w różnych stronach Kod / Konfrontacje dla VARCHARdanych (np Þ= kapitał Łacińskiej „Thorn” = SELECT CHAR(0xDE)), to może trzeba należą do klasy postaci: [a-z0-9, Þ]. Oczywiście, jakie byłyby te dodatkowe znaki, zależnie od strony kodowej.

Należy również pamiętać, że zarówno typ sortowania (SQL Server vs Windows), jak i ustawienia czułości (wielkość liter, akcent itp. Wrażliwe vs niewrażliwe) będą miały wpływ na to, które znaki są zawarte w danym zakresie. Na przykład SQL Server Collations sortuje wielkie i małe litery w odwrotnej kolejności niż Windows Collations. To znaczy, zakładając, że dla obu typów zestawień rozróżniana jest wielkość liter, jedno zrobi, AaBb...a drugie zrobi aAbB.... Efekt będzie taki, że abędzie w zasięgu A-Zjednego z nich, ale nie drugiego. A zakres a-Znie pasuje do żadnych znaków w binarnym Układanie (jeden kończy się albo _BINalbo _BIN2, ale nie używać _BIN) Biorąc pod uwagę, że wartość Awynosi 65 iawynosi 97, stąd jest to nieprawidłowy zakres od 97 do 65 ;-). Istnieje o wiele zbyt wiele odmian, aby podać tutaj przykłady, więc wkrótce postaram się opublikować szczegółowe wyjaśnienie na moim blogu (a następnie zaktualizuję ten link do niego). Jeśli jednak będziesz surowo akceptować tylko znaki amerykańskie w języku angielskim (nawet jeśli możesz otrzymać prawidłowe litery z innych języków), najlepszym rozwiązaniem będzie prawdopodobnie użycie następującego wzoru i sortowania:

LIKE '%[^A-Za-z0-9, ]%' COLLATE Latin1_General_100_BIN2

Teraz, jeśli wspierasz NVARCHARdane i możesz uzyskać znaki „słów” z różnych języków, to T-SQL nie będzie zbyt pomocny, ponieważ nie ma realnego sposobu na rozróżnienie tych rzeczy. W takim przypadku należy użyć wyrażenia regularnego (RegEx) - w szczególności Replacemetody / funkcji - i są one dostępne tylko za pośrednictwem SQLCLR. Poniżej pokazano przykład zastąpienia kilku „znaków specjalnych”, ale pozostawiając wszystkie prawidłowe litery w co najmniej jednym języku:

DECLARE @Test NVARCHAR(500);
SET @Test = N'this$is%a<>TEST,;to}⌡↕strip╞╟╚══¶out_ç_ƒ▀ special-ij-೫-chars-舛-დ-א-B';
SELECT SQL#.RegEx_Replace4k(@Test, N'[\W\p{Pc}-[,]]', N' ', -1, 1, NULL); 

Zwroty:

this is a  TEST, to   strip      out ç ƒ  special ij ೫ chars 舛 დ א B

Wyrażenie RegEx oznacza:

• \W= „ucieczka” RegEx oznaczająca „dowolny znak niebędący słowem”
• \p{Pc}= „kategoria” w standardzie Unicode „Interpunkcja, łącznik” (jest to konieczne tylko w przypadku dopasowania, ponieważ ta „kategoria” jest specjalnie wykluczona przez funkcję zmiany znaczenia \W)
• -[,]= odejmowanie klas (jest to konieczne, aby wykluczyć przecinki z dopasowywania jako „specjalne”, ponieważ są uwzględniane w \Wznaku zmiany znaczenia)

Możesz zrobić aktualizację tabeli, po prostu wydając:

UPDATE tbl
SET    tbl.field = SQL#.RegEx_Replace4k(tbl.field, N'[\W\p{Pc}-[,]]', N' ', -1, 1, NULL)
FROM   tbl
WHERE  SQL#.RegEx_IsMatch4k(tbl.field, N'[\W\p{Pc}-[,]]', 1, NULL) = 1;

Pamiętaj, że dla tych przykładów użyłem dwóch funkcji dostępnych w bibliotece SQL w wersji darmowej funkcji SQLCLR, które utworzyłem (ale znowu są one bezpłatne). Zauważ też, że użyłem wersji „4k”, które są szybsze ze względu na użycie NVARCHAR(4000)zamiast NVARCHAR(MAX)typów parametrów. Jeśli używasz danych NVARCHAR(MAX), po prostu usuń „4k” z nazw funkcji.

Zobacz także:

• Klasy postaci RegEx

Mam tutaj post , który robi coś podobnego .

Zasadniczo używam rekurencyjnego CTE do ciągłego zapętlania, zastępując jedną „złą” postać na raz. Używam STUFF, aby usunąć 1 znak (chociaż możesz go użyć, aby zastąpić spacją), a PATINDEX, aby znaleźć lokalizację znaku, który chcę usunąć. Możesz go nieco zmodyfikować, aby zrobić to, czego szukasz. Jednak tworzy „dobrą” listę, w rzeczywistości nie aktualizuje istniejącej listy.

DECLARE @Pattern varchar(50) = '%[^A-Za-z0-9, ]%';

WITH FixBadChars AS (SELECT StringToFix, StringToFix AS FixedString, 1 AS MyCounter, Id
                FROM BadStringList
                UNION ALL
                SELECT StringToFix, Stuff(FixedString, PatIndex(@Pattern, 
                    FixedString COLLATE Latin1_General_BIN2), 1, ' ') AS FixedString, 
                    MyCounter + 1, Id
                FROM FixBadChars
                WHERE FixedString COLLATE Latin1_General_BIN2 LIKE @Pattern)
SELECT StringToFix, FixedString, MyCounter, Id
FROM FixBadChars
WHERE MyCounter = 
        (SELECT MAX(MyCounter) 
        FROM FixBadChars Fixed
        WHERE Fixed.Id = FixBadChars.Id)
OPTION (MAXRECURSION 1000);

Powinieneś być w stanie zmodyfikować dolną część, aby wykonać aktualizację, a nie tylko zapytanie, ale tak naprawdę tego nie próbowałem. Jestem całkiem pewien, że wyglądałoby to tak:

UPDATE FixBadChars
SET StringToFix = FixedString
WHERE MyCounter = 
        (SELECT MAX(MyCounter) 
        FROM FixBadChars Fixed
        WHERE Fixed.Id = FixBadChars.Id)
OPTION (MAXRECURSION 1000);

W zakresie skalowalności zwróciłem ~ 170 tys. Oczyszczonych wierszy w mniej niż 30 sekund. Znów nie jestem pewien, czy zrobię aktualizację, ale to było na moim laptopie, który jest dość wolny z ram tylko 6 GB.

Declare @String nchar(2000)='hg$%^AB,.:23ab-=+'

Declare @NewString VARCHAR(2000)=''
Declare @Lenght int=LEN(@String)
Declare @Index int=1

WHILE (@Index <= @Lenght)
BEGIN
    Declare @Letter nchar(1)=Substring(@String,@Index,1);
    Declare @ASCII int=ASCII(@Letter);
    If((@ASCII >= 48 and @ASCII <= 57) or (@ASCII >= 97 and @ASCII <= 122) or (@ASCII >= 65 and @ASCII <= 90))
    BEGIN
        SET @NewString += @Letter
    END
    ELSE
    BEGIN
        SET @NewString += ' '
    END
    SET @Index+=1

END
Select @NewString