SQL Server 檢查欄位資料是否包含奇特的 ASCII Char (Len, Datalength, Char & ASCII)

2022-10-07

筆記 SQL Server 如何檢查資料表的資料包含了奇特的 ASCII Char，例如 LF 以及 CR 造成應用程式的執行問題。

說明

SELECT
  ColumnName,
  len(ColumnName),
  datalength(ColumnName),
  len(trim(char(10)+char(13) from ColumnName))
FROM
  TableName
WHERE ColumnName like '%' + char(10) + '%' or ColumnName like '%' + char(13) + '%'

LEN

Len 可以取得欄位資料的字元數，如果肉眼看似 5 個字元的字串，但卻顯示 6 個或 7 個，甚至更多的字元，這個時候就可以能包含到奇特的 ASCII Char。

至於是什麼 ASCII Char？可以使用 View non-printable unicode characters 來做確認。

或使用黑暗執行緒所設計的中文編碼解析線上版。

DATALENGTH

Datalength 顯示的是欄位資料的位元數，會與資料的編碼以及資料類型有關。例如 UTF-16 的 Nvarchar 每一個字元都會使用 2 Bytes，因此當 Len 為 6 的資料欄位，會在 Datalength 顯示為 12。

Char

Char 可以輸入十進位數值 (Dec) 得到 ASCII 的值，可以藉此來表示 ASCII 特殊的 Char。

SELECT 'Hello' + char(10)
SELECT ColumnName Where ColumnName like '%' + char(10) + '%'

ASCII

ASCII 可以取得字元在 ASCII 上十進位的值。

SELECT ASCII('A')
-- DEC 65

如果對中文字元使用，因為中文字元佔 2 個 Bytes，只會得到該字元 BIG5 的第一個 Bytes 的十進位值，例如：

SELECT ASCII('A')
-- DEC 184
-- HEX B8 EA (BIG5 資)
-- B8 = 184

相似的函式還有 UNICODE，回應該字元 UTF-16 的十進位值。

SELECT UNICODE('資')
-- DEC 36039
-- HEX 8CC7 (Unicode 資)

TRIM

TRIM 預設上會移除空白字元，但可以藉由下列語法來調整要移除的指定字元（注意只會移除頭尾字元）：

SELECT trim('13! ' from '1235')
-- 235
SELECT trim(char(10)+char(13) from ColumnName)

ASCII Table

char(10) 為 LF、char(13) 為 CR

相關連結

SQL Server Integrated Service 初探

SQL Server 閃電般快速查詢指南⚡

SQL Server 周邊工具彙整筆記

SQL Server 學習資源筆記