用 MS SQL 分出地址中的 路/街/大道

在 iT邦幫忙 有人提出的問題: SQL 截取特定字串

要先解釋一下架構, 地址的組成, 最低階的是文字(漢字/數字等), 往上會依行政需要而區分. 難的就是行政上分 鄉鎮市區 區里鄰 路街大道 巷弄衖號 等, 另外還會有些例外狀況如: 異體字, 有些漢字在Big5編碼未收錄,

以較正式的做法, 是找 民政單位 的 鄉鎮市區 等單位的清單, 或 中華郵政 的清單來比對.
以比較簡易的做法, 則是使用民政單位的階層來判斷.



而民政的地址階層又是另一個問題, 例如:

  1. 常見的地址是 xx區xx路nn巷 , 但有些地方有 xx區xx巷 , 而沒有 路/街/大道 , 而這些巷名又是漢字.
  2. 有些地址會有 村/里/鄰 , 有些沒有.
  3. 有些地址直接以 xx村nn號 標示, 但有些是 xx村xxnn(號) .
所以這些規則需要先整理過, 大致上是:

  1. 縣/市
  2. 鄉/鎮/市/區
  3. 村/里
  4. 鄰
  5. 路/街/大道/巷
  6. 巷
  7. 弄
  8. 衖
  9. (之)號(之)
  10. 樓(之)
而原問題是要找出 路名 , 相對完整的地址分析是比較簡單, 不過原問題是要使用 MS SQL 的查詢, 非 Stored Procedure , 所以語法必須一次處理完成, 無法使用 變數 來暫存.

依原問題需要, 只需要從地址中抓出路名, 所以從 路/街/大道/巷 更小單位的部份可以捨去, 但更大單位可能有:

  1. 高雄市xx路
  2. 高雄市xx區xx路
  3. 高雄市xx區xx里xx路
  4. 高雄市xx區xx里xx鄰xx路
所以大單位的部份要能夠剔除, 因此大概的架構就是:
  1. 找出 路/街/大道/巷 , 剔除小單位.
  2. 把更大單位剔除.
在 MS SQL 有幾個指令可以用: charindex 從字串找出特定字的位置, substring 從特定位置切除字串, case 篩選不同條件. 排列後就如下:
  1. 如果地址有 路/街/大道/巷 的字串, 就繼續下一階動作.
  2. 如果地址有 鄰/里/村/區/鄉/鎮/市/縣 的字串, 才進行切除.
寫出來的程式如下(少處理了巷):
create table #temp_add (
Address nvarchar(500)
);

insert into #temp_add values (
N'台南市安定區保定路333號'
);

select address,   CASE
      WHEN charindex(N'路',address) >0 THEN
case
when charindex(N'鄰',address) >0 then ''+substring(address,charindex(N'鄰',address)+1,charindex(N'路',address)-charindex(N'鄰',address))
when charindex(N'里',address) >0 then ''+substring(address,charindex(N'里',address)+1,charindex(N'路',address)-charindex(N'里',address))
when charindex(N'村',address) >0 then ''+substring(address,charindex(N'村',address)+1,charindex(N'路',address)-charindex(N'村',address))
when charindex(N'區',address) >0 then ''+substring(address,charindex(N'區',address)+1,charindex(N'路',address)-charindex(N'區',address))
when charindex(N'鄉',address) >0 then ''+substring(address,charindex(N'鄉',address)+1,charindex(N'路',address)-charindex(N'鄉',address))
when charindex(N'鎮',address) >0 then ''+substring(address,charindex(N'鎮',address)+1,charindex(N'路',address)-charindex(N'鎮',address))
when charindex(N'市',address) >0 then ''+substring(address,charindex(N'市',address)+1,charindex(N'路',address)-charindex(N'市',address))
when charindex(N'縣',address) >0 then ''+substring(address,charindex(N'縣',address)+1,charindex(N'路',address)-charindex(N'縣',address))
end
      WHEN charindex(N'街',address) >0 THEN
case
when charindex(N'鄰',address) >0 then ''+substring(address,charindex(N'鄰',address)+1,charindex(N'街',address)-charindex(N'鄰',address))
when charindex(N'里',address) >0 then ''+substring(address,charindex(N'里',address)+1,charindex(N'街',address)-charindex(N'里',address))
when charindex(N'村',address) >0 then ''+substring(address,charindex(N'村',address)+1,charindex(N'街',address)-charindex(N'村',address))
when charindex(N'區',address) >0 then ''+substring(address,charindex(N'區',address)+1,charindex(N'街',address)-charindex(N'區',address))
when charindex(N'鄉',address) >0 then ''+substring(address,charindex(N'鄉',address)+1,charindex(N'街',address)-charindex(N'鄉',address))
when charindex(N'鎮',address) >0 then ''+substring(address,charindex(N'鎮',address)+1,charindex(N'街',address)-charindex(N'鎮',address))
when charindex(N'市',address) >0 then ''+substring(address,charindex(N'市',address)+1,charindex(N'街',address)-charindex(N'市',address))
when charindex(N'縣',address) >0 then ''+substring(address,charindex(N'縣',address)+1,charindex(N'街',address)-charindex(N'縣',address))
end
      WHEN charindex(N'大道',address) > 0 THEN
case
when charindex(N'鄰',address) >0 then ''+substring(address,charindex(N'鄰',address)+1,charindex(N'大道',address)-charindex(N'鄰',address)+1)
when charindex(N'里',address) >0 then ''+substring(address,charindex(N'里',address)+1,charindex(N'大道',address)-charindex(N'里',address)+1)
when charindex(N'村',address) >0 then ''+substring(address,charindex(N'村',address)+1,charindex(N'大道',address)-charindex(N'村',address)+1)
when charindex(N'區',address) >0 then ''+substring(address,charindex(N'區',address)+1,charindex(N'大道',address)-charindex(N'區',address)+1)
when charindex(N'鄉',address) >0 then ''+substring(address,charindex(N'鄉',address)+1,charindex(N'大道',address)-charindex(N'鄉',address)+1)
when charindex(N'鎮',address) >0 then ''+substring(address,charindex(N'鎮',address)+1,charindex(N'大道',address)-charindex(N'鎮',address)+1)
when charindex(N'市',address) >0 then ''+substring(address,charindex(N'市',address)+1,charindex(N'大道',address)-charindex(N'市',address)+1)
when charindex(N'縣',address) >0 then ''+substring(address,charindex(N'縣',address)+1,charindex(N'大道',address)-charindex(N'縣',address)+1)
end
   END   
from #temp_add

drop table #temp_add
於
標籤： , , ,