SQL 解鎖 - 美國 ZIP Code 郵遞區號

處理美國地區 ZIP Code 郵遞區號相關的資料問題應該是許多外企資料分析師每天都會處理的問題，ZIP Code 通常是在做區域相關分析的最小、訂單能記錄到最細的標準化單位，引此了解一些處理ZIP Code的眉角對於資料處理、分析、視覺化，以至於建構模型是非常重要的喔! 本篇就讓我們拿一些實例與各位分享。

什麼是Zip Code

ZIP Code 是美國郵政使用的一種郵遞區號，一般常以大寫ZIP。ZIP是英語Zone Improvement Plan（地區改進計劃）的簡稱，它暗示郵件可以以更有效率及快捷地送到目的地。最基本的ZIP編號包括五個號碼，隨後增加了四個號碼，使郵件可以更精確地傳送到目的地。增加號碼後的ZIP編號稱為"ZIP+4"。ZIP編號曾被美國郵政註冊成一個商標，但其註冊至今已經過期。

格式

如同上述，ZIP code 通常是純數字格式，標準的格式是五位數字，例如"28230" ，和"ZIP+4"，比如"999290531"或"99929-0531"。美國東北某些區域的ZIP code是以0開頭，比如"01172"或"012402123"。因為連接符號"-"和開頭0的性質，即便主要格式是數字，ZIP code 欄位常常在資料庫裡還是會以文字格式儲存。

JOIN

• 兩表都是九位數字，但其中一張表有連接符號"-"，另一張沒有，這樣是幾乎JOIN不到資料的。或是，一張表是5位數字，另一張是九位數字，這樣也是JOIN不到資料的。

• 兩表都是五位數字，但其中一張是數字格式，另一張是文字格式，有些軟體會報錯，有些會連接可以連接到的。若確定兩張表都是五位數字且沒有連接符號"-"，把文字格式cast成數字是可行的。

• 一張表是五位數字，另一張九位數字但沒有連接符號"-"，兩張表皆為文字格式。因為位數不同，若不做left(ZIPCODE,5)處理是連接不到彼此的。

• 類似上一個狀況，但兩張表皆為數字格式，這時作left(ZIPCODE,5)會相當危險，因為你可能會把錯誤的把11231234(前5位是11231)連結到11231。

• 綜合各種狀況的資料表，有些資料表有5碼+9碼，有些只有5碼，有些是數字格式，有些是文字格式，讓處理起來讓人不是非常放心，我到底有沒有JOIN對的資料啊?

一勞永逸

我這裡提供了一個流程讓您不用再去判斷上述狀況(假設資料只要JOIN上5碼的ZIP code)。

首先，新增一個欄位叫zipcode_adj (文字格式)，先把多餘的符號去掉。

zipcode_adj = replace([zipcode], '-' , '')

接著，如果有發現出現4碼或8碼長度的資料，在前頭補一個0。

Update TABLE1​

set zipcode_adj = '0' + zipcode_adj

where  len(zipcode_adj) in (4,8)

把兩個(或以上)的資料集都按照此方法處理後再作JOIN，就可避免上述的失誤囉。

UPDATE A
SET A.Col  = B.Col
FROM TABLE1​ A
INNER JOIN TABLE2​ B
ON
LEFT(A.zipcode_adj ,5) =  LEFT(B.zipcode_adj,5)

遵循著我們上述的思考脈絡，希望能對您思考這類問題有所助益，也謝謝您撥冗完食。這些內容是根據我過去在外商的經驗，非常歡迎業界先進留言與我交流，我也會不定期補充我的觀點，也歡迎Follow我的Threads，持續都會有這類問題討論和生產力提升的點子喔!