筆記 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 |