2022-10-07

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

說明
ASCII Table
相關連結

筆記 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 來做確認。

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

需要注意的是在黑大的編碼解析工具上，會將換行符號 LF, CR 解讀為空白並編碼為 Hex 20 (Dec 32)。

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

Char	Dec	Hex	Oct
NUL	000	00	000
SOH	001	01	001
STX	002	02	002
ETX	003	03	003
EOT	004	04	004
ENQ	005	05	005
ACK	006	06	006
BEL	007	07	007
BS	008	08	010
HT	009	09	011
LF	010	0A	012
VT	011	0B	013
FF	012	0C	014
CR	013	0D	015
SO	014	0E	016
SI	015	0F	017
DLE	016	10	020
DC1	017	11	021
DC2	018	12	022
DC3	019	13	023
DC4	020	14	024
NAK	021	15	025
SYN	022	16	026
ETB	023	17	027
CAN	024	18	030
EM	025	19	031
SUB	026	1A	032
FSC	027	1B	033
FS	028	1C	034
GS	029	1D	035
RS	030	1E	036
US	031	1F	037
SPACE	032	20	040

說明

ASCII Table

相關連結