用空格替換列中的特殊字元

我正在嘗試編寫一個用空格替換特殊字元的查詢。下面的程式碼有助於辨識行。（字母數字字元、逗號和空格有效）：

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

如何將替換功能集成到 select 語句中，以便結果集中除字母數字、逗號和空格之外的所有字元都替換為“”（空格）。這個不行：

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

如果您保證只使用美國英語字母表的 26 個字母（大寫和小寫版本），那麼可以肯定，您可以使用LIKE和/或PATINDEX簡單的範圍表示法[a-z]（您不會使用不區分大小寫的排序規則時需要使用大寫的“Z”）。

但是，如果您可能會得到在 en-US 字母表中找不到但在各種程式碼頁/VARCHAR數據整理中可用的字元（例如Þ= 拉丁大寫“Thorn”= SELECT CHAR(0xDE)），那麼您可能需要將這些字元包含在字元類中：[a-z0-9, Þ]. 當然，這些額外的字元是基於每個程式碼頁的。

此外，請注意排序規則類型（SQL Server 與 Windows）和敏感度設置（大小寫、重音等敏感與不敏感）都會影響特定範圍內包含的字元。例如，SQL Server 排序規則以與 Windows 排序規則相反的順序對大寫和小寫字母進行排序。意思是，假設兩種排序規則都區分大小寫，一種會做AaBb...，另一種會做aAbB...。效果將a是在其中一個範圍內A-Z，但不在另一個範圍內。鑑於a-Z的值為65_BIN和__BIN2``_BIN``A``a是 97，因此它是 97 到 65 的無效範圍；-)。這裡有太多的變化可以舉出例子，所以我會盡快在我的部落格上發布一個詳細的解釋（然後會用它的連結更新它）。但是，如果您要嚴格只接受美國英語字元（即使您可能會從其他語言中獲得有效字母），那麼您最好的選擇可能是使用以下模式和排序規則：

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

現在，如果您正在支持NVARCHAR數據並且可以從各種語言中獲取“單詞”字元，那麼 T-SQL 將沒有多大幫助，因為它沒有真正的方法來區分這些東西。在這種情況下，您應該使用正則表達式 (RegEx)——特別是Replace方法/函式——而這些只能通過 SQLCLR 獲得。下面顯示了一個替換幾個“特殊”字元的範例，但將所有有效字母保留在至少一種語言中：

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); 

回報：

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

RegEx 表達式意味著：

• \W= 一個正則表達式“轉義”，意思是“任何非單詞字元”
• \p{Pc}=“標點符號，連接符”的Unicode“類別”（這僅用於匹配，因為此“類別”被\W轉義明確排除）
• -[,]= 類減法（這需要將逗號從匹配中排除為“特殊”，因為它們包含在\W轉義中）

您只需發出以下命令即可更新表：

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;

請注意，對於這些範例，我使用了我創建的免費版SQL# SQLCLR 函式庫中提供的兩個函式（但同樣，這些都是免費的）。另請注意，由於使用NVARCHAR(4000)而不是NVARCHAR(MAX)參數類型，我使用了更快的“4k”版本。如果您的數據正在使用NVARCHAR(MAX)，則只需從函式名稱中刪除“4k”。

另請參閱：

• 正則表達式字元類

引用自：https://dba.stackexchange.com/questions/126127