iT邦幫忙

0

SQL 截取特定字串

小弟 MSSQL 新手
想請教
假設有一地址 例:桃園市桃園區龍祥里4鄰守法路5號8樓之一

我想要把 守法路 截取出來做得到嗎?
一開始是想用
SELECT
STRING(地址,CHARINDEX('鄰',地址)+1,CHARINDEX('路',地址)-CHARINDEX('鄰',地址))
FROM 地址
但是做不出來
因為每個地址都不一樣長度
請各位前輩指點

0
slime
iT邦大師 1 級 ‧ 2019-06-25 17:00:43
最佳解答

(無法處理 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


slime iT邦大師 1 級 ‧ 2019-06-26 14:53:21 檢舉

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


slime iT邦大師 1 級 ‧ 2019-06-26 14:55:27 檢舉

建議比較完整的做法:

  1. 先了解民政單位的地址編排, 從 鄉/鎮/市/區 里/鄰 路/街/大道 等階層開始拆.
  2. 鄉鎮市區 里鄰 路/街 等資料有清單可以下載.
  3. 從大階層逐步拆到小階層, 最後查到 路/街 在清單內, 則是符合需求.
4
浩瀚星空
iT邦大師 1 級 ‧ 2019-06-25 12:35:55

你可以往正則的方向去想。
不過其實可以提前告訴你。很難做到就是了。

我早期也有想要研究正則的分離方式。
但台灣的路名是在太過奇特。有特例的情況。

所以後來還是用了郵政路名的資料庫來解決。而不直接從地址字串處理。

3
japhenchen
iT邦新手 4 級 ‧ 2019-06-25 13:34:12

如果你可以跳脫純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']");
                        }
                    }
                }
            }
        }
1
小魚
iT邦高手 1 級 ‧ 2019-06-25 13:55:25

難度頗高,
台灣甚至還有地址連個 路 字都看不到的...
中華郵政的做法應該是把所有的都列出來...

0
vegalou
iT邦新手 4 級 ‧ 2019-06-25 14:27:08

一個迴圈不就踢出來了

for(var ii=0;ii<mArecord.length;ii++){
 if(mArecord[ii].indexOf('poo')) console.log("^o^");
}

regEx 還可以模擬比對

1
Ks
iT邦新手 4 級 ‧ 2019-06-25 15:14:14
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

修正,好像也不可行~因為路也有可能出現 鄰 字,當我沒貼 哈哈

/images/emoticon/emoticon06.gif

我要發表回答

立即登入回答