查詢以查找在給定範圍內包含 ASCII 字元的行

我正在使用來自另一個主題的一些腳本，但接受的答案不適用於我的所有數據場景。我會在原始的How to check for Non-Ascii Characters文章上問我的問題，但到目前為止我還沒有足夠的聲譽來評論或投票。

問題：

• 為什麼接受的答案不適用於 char(150)？
• 有沒有辦法修復接受的答案？

我的測試

我使用範例數據、來自答案之一的儲存過程以及用於展示問題的查詢創建了SQL Fiddle 。

查詢一：sample_table

-- Note: The "bad dash" row has char(150)

SELECT * FROM sample_table;

+-------------------+
|    DataColumn     |
+-------------------+
| test - good dash  |
| test – bad dash   |
+-------------------+

查詢 2：John的另一個答案顯示了包含 char(150) 的“壞破折號”行：

SELECT dbo.Find_Invalid_Chars(DataColumn) [Invalid Characters]
FROM sample_table
WHERE dbo.Find_Invalid_Chars(DataColumn) IS NOT NULL;

+----------------------+
|  Invalid Characters  |
+----------------------+
| test [150] bad dash  |
+----------------------+

查詢 3：Martin Smith接受的答案沒有返回結果：

SELECT DataColumn AS [Bad Data]
FROM sample_table
WHERE DataColumn LIKE '%[' + CHAR(127)+ '-' +CHAR(255)+']%' COLLATE Latin1_General_100_BIN2;

+------------+
| [Bad Data] |
+------------+

-- No rows returned.

結論

不幸的是，我經常需要在我無法在其中創建儲存過程的數據庫中查找某個範圍內（或之外）的字元。我真的很想為已接受的答案或不需要創建的簡單腳本找到一個修復程序任何對象（包括臨時表）。

有什麼建議麼？提前致謝。

**編輯 1：**該解決方案無法修改或添加數據庫中的任何對像或設置。我正在尋找一個獨立的查詢，它將在兩個CHAR()數字之間的範圍內選擇一個或多個字元的行，而不管提供的 ASCII 或擴展 ASCII 數字如何。

編輯 2： DataColumn 可以在VARCHAR或NVARCHAR中。我無法控制這一點，所以我希望找出一個適用於兩者的獨立查詢。查詢的目的是查找源表/列中某些軟體應用程序未正確處理的字元。應用程序正確解釋源，但有時會出現“標準”範圍之外的字元問題，儘管範圍因應用程序而異。

為什麼接受的答案不適用於 char(150)？

事實上，確實如此。問題是您的測試不好/無效。您測試列 ,DataColumn正在使用NVARCHAR而不是VARCHAR。字元本身適用於兩種數據類型，但由於在每種情況下的使用方式不同，行為不同：

• 在Find_Invalid_Chars()函式中（即“其他”答案），字元串被轉換回，VARCHAR因為這是該函式的輸入參數的數據類型。在這種情況下，它按預期工作（儘管我相信它可以比那個循環更有效地完成，但那是另一次了 ;-)
• 在LIKE查詢（即“接受”答案）中，擴展和連接的結果'%[' + CHAR(127)+ '-' +CHAR(255)+']%'實際上被轉換為NVARCHAR，因為這是它正在與之比較的列的數據類型（並且NVARCHAR具有更高的數據類型優先級），因此該LIKE函式沒有表現正如預期的那樣：CHAR(255)字元映射到不同的程式碼點，和/或CHAR(150)列中的字元本身映射到不同的程式碼點（CHAR(127)字元不會改變，因為它在標準 ASCII 範圍內）。在任何一種情況下，轉換為NVARCHAR都會導致“En Dash”字元（“-”）的數值不再在該範圍內。意思是，該LIKE函式正在尋找值y，127``x（其中x>= 128），y“En Dash”字元現在是 > x。而在VARCHAR, x= 255 和y= 150 中。

查看它是否有效的快速修復方法是將列的NVARCHAR數據類型 更改為（是的，只需刪除初始的“N”），然後重新建構模式，然後執行，查詢將按預期執行。DataColumn``VARCHAR``LIKE

以下內容可能有助於解釋為什麼製作測試列會NVARCHAR導致LIKE查詢與行不匹配：

SELECT UNICODE(CHAR(127)) AS [CHAR(127)],
      UNICODE(CHAR(150)) AS [CHAR(150)],
      UNICODE(CHAR(255)) AS [CHAR(255)];

/*
CHAR(127)     CHAR(150)     CHAR(255)
127           8211          255
*/

正如您在查詢下方的結果中看到的那樣，“壞破折號”是在儲存在列中時CHAR(150)變成的。而且，由於該謂詞使用二進制排序規則（在這種情況下通常是正確的做法），它查看的是程式碼點/值，而不是字元。因此，該子句正在尋找值介於 127 和 255 之間的字元，而 8211 通常不在該範圍內；-)。NCHAR(8211)``NVARCHAR``LIKE

PS請記住，該函式CHAR(150) 可以NULL根據您執行該函式的數據庫的預設排序規則返回不同的字元，甚至。這是因為VARCHAR數據是基於程式碼頁的，這些是由排序規則決定的，執行CHAR()函式時使用的排序規則是活動/目前數據庫的預設排序規則。這會影響值 128 - 255。無論排序規則如何，值 0 - 127 將始終返回相同的字元，因為這些字元是標準的 ASCII 字元集，並且在 SQL Server 支持的所有程式碼頁中都是相同的（儘管並非在所有程式碼中一般頁面）。

PPS ALSO，我只是注意到函式和查詢之間的邏輯略有不同（即連結問題的兩個答案）：CHAR(127)在函式中被認為是好的/有效的Find_Invalid_Chars()，但在LIKE查詢中被認為是壞的/無效的。如果是我，我會認為CHAR(127)它是標準 ASCII 字元集的一部分。但是，你需要決定你認為它是什麼。請注意這種差異，以防您確實需要LIKE稍微調整語法。

---

鑑於：

1. >

查詢的目的是查找源表/列中某些軟體應用程序未正確處理的字元。

和： 2. >

數據可以是 VARCHAR 或 NVARCHAR。

我會這樣說：

1. 您不想將NVARCHAR源數據轉換VARCHAR為，因為可能存在將無效源字元轉換為有效字元的“最佳匹配”映射，但您的一個或多個軟體應用程序可能不使用“最佳匹配”映射。

SELECT NCHAR(178) AS [Unicode], -- Superscript 2 (U+00B2)
      CONVERT(VARCHAR(5), NCHAR(178)
                  COLLATE SQL_Latin1_General_CP1_CI_AS) AS [CodePage-1252],
      CONVERT(VARCHAR(5), NCHAR(178)
                  COLLATE Turkmen_100_CI_AS) AS [CodePage-1250]

/*
Unicode    CodePage-1252    CodePage-1250
²          ²                2
*/

2. 與特定無效範圍內的字元相比，查找不在特定“有效”範圍內的字元可能更可靠*，*NVARCHAR尤其是在處理包含超過 256 個字元的字元時。
3. 如果“有效”範圍始終介於值 0 和 127 之間（因為這些值在兩種情況下都相同），則您可以使用單個查詢。但如果您需要指定高於 127 的值，則需要一個查詢 forVARCHAR和一個 for NVARCHAR。

說了這麼多：

• 以下查詢為 和 都返回包含至少一個不在0 到 127 範圍內的字元的VARCHAR行NVARCHAR。但是，它僅適用NVARCHAR於值高於 127 的列。

SELECT *
FROM   (VALUES (NCHAR(178)), (NCHAR(8211)), (N''), (NULL), (N'xy' + NCHAR(165)),
          (N'AA'), (N'mM' + NCHAR(999) + N'Nn'), (N'#!~()')) tmp(TestValue)
WHERE  tmp.[TestValue] LIKE N'%[^' + NCHAR(0) + N'-' + NCHAR(127)
         + N']%' COLLATE Latin1_General_100_BIN2;

/*
TestValue
²
–
xy¥
mMϧNn
*/

• 以下查詢還返回包含至少一個不在0 - 127 範圍內的字元的行，但僅適用於VARCHAR列。但是，它確實允許使用 128 到 255 之間的值。

SELECT *
FROM   (VALUES (CHAR(178)), (CHAR(150)), (''), (NULL), ('AA'), ('#!~()'),
       ('xy' + CONVERT(VARCHAR(5), NCHAR(165) COLLATE Latin1_General_100_BIN2)),
       ('mM' + CONVERT(VARCHAR(5), NCHAR(199) COLLATE Latin1_General_100_BIN2) + 'Nn')
      ) tmp(TestValue)
WHERE  tmp.[TestValue] LIKE '%[^' + CHAR(0) + '-' + CHAR(127)
         + ']%' COLLATE Latin1_General_100_BIN2;

/*
TestValue
²
–
xy¥
mMÇNn
*/

---

關於：

應用程序正確解釋源，但有時會出現“標準”範圍之外的字元問題，儘管範圍因應用程序而異。

1. 如果應用程序正確解釋源數據，我不確定我是否理解某些字元如何存在“問題”，除非您的意思是它們“大部分”正確解釋數據。
2. 聽起來像這樣因應用程序而異的範圍可能需要更詳細的調查，而不是像這樣的簡單問答格式。這種行為可能是由於他們使用不同的驅動程序進行連接（ODBC / OLEDB / 等），他們是用什麼語言編寫的，他們對所獲得的數據做出了什麼假設，等等。有些問題可能可以通過應用程序的配置（無程式碼更改）來解決，有些問題可能只能通過程式碼更改來解決，等等。

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