小弟 MSSQL 新手
想請教
假設有一地址 例:桃園市桃園區龍祥里4鄰守法路5號8樓之一
我想要把 守法路 截取出來做得到嗎?
一開始是想用
SELECT
STRING(地址,CHARINDEX('鄰',地址)+1,CHARINDEX('路',地址)-CHARINDEX('鄰',地址))
FROM 地址
但是做不出來
因為每個地址都不一樣長度
請各位前輩指點
已邀請的邦友 {{ invite_list.length }}/5
(無法處理 xx村nn號 或 xxnn號 這種)
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
VBA 解法:
B1~N1: 鄉 鎮 市 區 里 鄰 大道 路 街 巷 弄 號 end
A2~A9: 填入地址, 再執行以下的 VBA
A10: end
Sub address_divide()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim tempadd As String
i = 2
Do
If Cells(i, 1) = "end" Or Cells(i, 1) = "" Or i > 101 Then Exit Do
j = 2
Do
If Cells(1, j) = "end" Or Cells(1, j) = "" Or j > 16 Then Exit Do
On Error Resume Next
Cells(i, j) = WorksheetFunction.Search(Cells(1, j), Cells(i, 1))
j = j + 1
Loop
j = 2
k = 1
Do
If Cells(1, j) = "end" Or Cells(1, j) = "" Or j > 16 Then Exit Do
If Cells(i, j) > 0 Then
tempadd = Mid(Cells(i, 1), k, Cells(i, j) - k + 1)
k = Cells(i, j) + 1
Cells(i, j) = tempadd
End If
j = j + 1
Loop
i = i + 1
Loop
End Sub
建議比較完整的做法:
你可以往正則的方向去想。
不過其實可以提前告訴你。很難做到就是了。
我早期也有想要研究正則的分離方式。
但台灣的路名是在太過奇特。有特例的情況。
所以後來還是用了郵政路名的資料庫來解決。而不直接從地址字串處理。
如果你可以跳脫純MSSQL，搭配程式語言(我用C#或PYTHON)，把大部份有用到的資料SELECT到程式裡，再用正則表達式來切分地址，你會更好完成你想要的事
BUT.........跟前一位回答者的答案類似，台灣的地址千奇百怪，如果你想准確的切出你要的部份，蠻難的
BTW，這是我寫的網路爬蟲，用C#寫的，同事從某大周刊取得千大企業名稱，我再透過程式去爬奇摩股市、股市公開觀測資訊網、未上市股票資訊取得地址、電話，再把地址準確切成你想要看的市區鄉鎮路名跟巷弄編號，再從中華郵政換取3+2郵遞區號
public void 抓郵遞區號()
{
if (地址 != "")
{
HtmlAgilityPack.HtmlDocument doc;
Regex patZipCode = new Regex(@"(?<city>\D{1,2}[縣市](.*[鄉鎮市])*)(.*[區])*(?<road>.*[路街])(?<num>.*[號巷])");
HtmlWeb hw = new HtmlWeb();
doc = hw.Load($"http://zip5.5432.tw/zip/{地址}");
if (doc != null)
{
郵遞區號 = 抓網頁元素byID(doc.DocumentNode, "//span[@id='zipcode']");
}
if (郵遞區號 == "")
{
string 正規化地址 = "";
Match amat = patZipCode.Match(地址);
if (amat.Success)
{
doc = hw.Load($"http://zip5.5432.tw/zip/{amat.Groups["city"].Value}{amat.Groups["road"].Value}{amat.Groups["num"].Value}");
if (doc != null)
{
郵遞區號 = 抓網頁元素byID(doc.DocumentNode, "//span[@id='zipcode']");
}
}
}
}
}
DECLARE @sample nvarchar(100);
SELECT @sample = N'桃園市桃園區龍祥鄰里4鄰守法路5號8樓之一';
WITH reverse_temp AS
(
SELECT reverse(left(reverse(@sample), CHARINDEX(N'鄰', reverse(@sample)) -1)) as test
),
answer_temp AS
(
SELECT SUBSTRING
(
(SELECT test FROM reverse_temp),
(CHARINDEX(N'鄰', (SELECT test FROM reverse_temp)) + 1),
(CHARINDEX(N'路', (SELECT test FROM reverse_temp)) - CHARINDEX(N'鄰', (SELECT test FROM reverse_temp)))
) as answer
)
SELECT * FROM answer_temp;
https://rextester.com/live/VYW30815
修正,好像也不可行～因為路也有可能出現 鄰 字,當我沒貼 哈哈
iThome鐵人賽
看影片追技術