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


  1. 說明
  2. ASCII Table
  3. 相關連結

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

SQL Server Logo

說明

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)LFchar(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

相關連結

SQL Server Integrated Service 初探

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

SQL Server 周邊工具彙整筆記

SQL Server 學習資源筆記