被一個茶包攪和了一下,也學到點新東西,特別PO文留念。

在整理一些從Excel轉進來的手打資料(強調"手打"表示可能存在輸入誤差,這邊多一點那邊少一點的,用行語來說,就是”資料有點髒”)。舉例來說,整理過程中我要比對出訂單明細OrderDetail裡有沒有打錯訂單編號,做法是查詢OrderDetail中,抓OrderId沒有在訂單主檔(OrderEntry)出現者。

這可難不倒我,蛋糕一塊:
SELECT OrderID FROM OrderDetail WHERE OrderId NOT IN (SELECT OrderID FROM OrderEntry)

結果查出一筆來,假設是'DK09090001'好了,下一步就來看看訂單主檔裡正確的OrderId應是啥,再把它校正過來。SSMS裡有個很方便的功能,可以將查詢結果的欄位內容用滑鼠點選拖到編輯區檢視:

結果讓我有些詫異,VARCHAR欄位為什麼後面會有多餘的空白? 在SQL Server裡,尾端的空白一律會被忽略才對? 而我在DK09090001 前後補上指令,寫成SELECT LEN('DK09090001 ')得到的結果是10,研究了一下,依LEN的定義,尾端空白不計入長度,這麼說來,最後一位是空白沒錯。

雖然很狐疑為什麼尾端空白會被保留下來(SQL有蟲蟲?),我下了一個UPDATE OrderEntry SET OrderId = RTRIM(OrderId),心想就算有Bug,來個全面消毒,有病治病,沒病強身總沒錯吧?

執行完畢,重跑一開始的比對查詢,卻發現OrderId還是對不上。怎麼會有無法RTRIM掉的空白? 反覆做了好幾次(其實這有失理智,SQL語法做一次不成功,做一百次也不會成功的,當然,阿飄月期間或許會有例外),還是無法解除這個怪異的狀況。

冷靜下來,我想到 "會不會結尾其實不是空白,所以無法被RTRIM"?

一個指令讓真相大白:

原來,OrderId最後一個字元是0xoa的換行符號,無法被RTRIM掉是合理的,而VARCHAR不保留尾端空白的鐵律也沒有任何例外。之前被呼嚨了這麼久,關鍵在於SSMS查詢介面UI裡特殊字元被轉成了空白處理,所以拖拉欄位值到編輯區的動作並沒有確實反映欄位真正的內容。

茶包已滅,收隊回家。


Comments

# by 四成收入

>> 原來,OrderId最後一個字元是0xoa的換行符號,無法被RTRIM掉是合理的 我覺得你對SSMS的要求太低了。如你所說,0xoa是一個換行符號(NEWLINE),而NEWLINE一般被視為WHITESPACE的一種。所以照道理,RTRIM()應該除掉所有的WHITESPACE,而不是狹義的SPACE。

# by Jeffrey

to 四成收入, 倒還好耶,可能是因為我所接觸的語言中(.NET, VBScript, VBA),trim預設都只會對0x20空白下手(或許在其他語言裡的慣用做法不同),如果T-SQL裡平平是"TRIM"卻變得如此凶猛,whitespace通殺,我反而會有點不習慣。如果要一口氣處理掉"whitespace",我應該會選擇使用Regular Expression。

# by 四成收入

>> 可能是因為我所接觸的語言中(.NET, VBScript, VBA),trim預設都只會對0x20空白下手(或許在其他語言裡的慣用做法不同) 如果GOOGLE 一下trim whitespace 你可以發現幾乎所有主要語言如有提供trim()的libray function都會移除whitespace.

# by Michael

版主你好, 我也遇到一樣的問題,拜讀你的文章後檢測發現如同你所說,最後一個字元為0xoa的換行符號, 請問目前有solution嗎? 我想破頭也想不出來 我是想在資料庫端就filter掉. ex. select XXX from tablename where XXX = 'Lock' ->但是偏偏XXX是'Lock ', 所以一直抓不出來 如果有solution麻煩你回答一下~ 感恩~

# by Jeffrey

to Michael, 如果確定所有的0x0a都是多餘的,下個指令清理門戶: UPDATE tablename SET XXX=REPLACE(XXX, CHAR(10), ''),應該就可以了

# by Michael

感恩~我會試看看的.. 非常感謝你的回答

Post a comment