SQL 截取特定字串

ms sql

SQLMASTER 2019-06-25 12:18:47 ‧ 4222 瀏覽

分享至

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

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

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

5 個回答

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

回應 2
分享
檢舉

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 檢舉

建議比較完整的做法:

先了解民政單位的地址編排, 從鄉/鎮/市/區里/鄰路/街/大道等階層開始拆.
鄉鎮市區里鄰路/街等資料有清單可以下載.
從大階層逐步拆到小階層, 最後查到路/街在清單內, 則是符合需求.

登入發表回應

㊣浩瀚星空㊣

iT邦大神 1 級 ‧ 2019-06-25 12:35:55

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

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

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

回應
分享
檢舉

登入發表回應

japhenchen

iT邦超人 1 級 ‧ 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']");
                        }
                    }
                }
            }
        }

回應
分享
檢舉

登入發表回應

小魚

iT邦大師 1 級 ‧ 2019-06-25 13:55:25

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

回應
分享
檢舉

登入發表回應

不明

【＊＊此則訊息已被站方移除＊＊】

iT邦新手 3 級 ‧ 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

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