[MSSQL] 取出局部字串-利用ParseName函數

本範例將介紹利用PARSENAME函數可擷取sysname物件中部分物件名稱之特性(四段式命名)，用以取得特定格式字串之子字串。
以下應用於擷取電腦IP網址第3段子網段為例，先以常用SUBSTRING取得並列SQL及結果如下：

SQL	執行結果
SELECT IP    , SUBSTRING(IP, 9, CHARINDEX('.', IP, 9)-9) "Sub"    , CHARINDEX('.', IP, 9)-9                    "Len" FROM (VALUES ('192.168.10.1')               , ('192.168.1.32')               , ('192.168.2.23')               , ('192.168.1.15')               , ('192.168.2.12')               ) AS IPs (IP)

  

再來嘗試以PARSENAME達成前述相同目的，PARSENAME函數可擷取sysname物件的物件名稱（Object name）、擁有者名稱（Schema name）、資料庫名稱（Database name）及伺服器名稱（Server name） 等四個部分，PARSENAME 函數並不會指出指定名稱的物件是否存在，只會傳回指定物件名稱的指定部份，函數語法如下 :

PARSENAME ( 'object_name' , object_piece )

object_name:擷取指定物件部分的物件名稱。 

object_name 是 sysname，此參數是一個選擇性限定的物件名稱，物件名稱應包含（非絕對）四個部分：伺服器名稱、資料庫名稱、擁有者名稱和物件名稱。

object_piece: 指定傳回的物件部分。 object_piece 的類型是 int，值及所取得之物件說明如下：

1 = 物件名稱

2 = 結構描述名稱

3 = 資料庫名稱

4 = 伺服器名稱

以下利用PARSENAME函數取得master.dbo.spt_values四段之部分物件，前逑sysname字串中只含有三段，未包含伺服器名稱（Server name），語法中取得第4部分之傳回值為NULL，由函數定義或回傳結果可得知，函數之物件擷取是由小（1 : 物件名稱）而大（4：伺服器名稱），也代表字串由右至左 (\)進行擷取，即物件字串不足時，則自動捨去並回傳NULL。

資料庫物件	PARSENAME函數用法/結果
	SELECT PARSENAME('master.dbo.spt_values', 1)       , PARSENAME('master.dbo.spt_values', 2)       , PARSENAME('master.dbo.spt_values', 3)       , PARSENAME('master.dbo.spt_values', 4)

以下將應用於截取第3、4段子網段，第3段子網段是代表由右向左計數之第2部分，第4段子網段代表右側之第1部分，SQL及結果如下:

SQL	執行結果
SELECT *      , PARSENAME(IP, 2) "Sec3 (P:2)"      , PARSENAME(IP, 1) "Sec4 (P:1)" FROM (VALUES ('192.168.10.1')               , ('192.168.1.32')               , ('192.168.2.23')               , ('192.168.1.15')               ) AS IPs (IP) ORDER BY CAST(PARSENAME(IP, 2) AS INT)           , CAST(PARSENAME(IP, 1) AS INT)

以下範例將分析PARSENAME函數特性分析，SQL及結果如下:

SQL	執行結果
SELECT *      , PARSENAME(Val, 1)  "-1"      , PARSENAME(Val, 2)　"-2"      , PARSENAME(Val, 3)　"-3"      , PARSENAME(Val, 4)　"-4" FROM (VALUES ('A1.A2.A3.A4')            , ('A1-A2-A3-A4')            , ('B1.B2.B3')            , ('C1.C2.C3.C4.C5')        ) AS Test(Val) WHERE 1=1

由以上結果可得以下結論

1.小數點（.）為分隔符號

2.由右向左取得子字串

3. 最多取得個4個子段結果

ü   超過4段

傳回一律為NULL值

ü   小於4段

由右至左開始擷取，不足則回傳NULL。

以下使用PARSENAME查詢電話號碼為行動電話之資料，如下：

SELECT *

      , '0' + PARSENAME(REPLACE(Phone, '-', '.'), 3)

        + STUFF(PARSENAME(REPLACE(Phone, '-', '.'), 2), 3, 0, '-')

        + STUFF(PARSENAME(REPLACE(Phone, '-', '.'), 1), 2, 0, '-') 

FROM (VALUES('886-2-2222-0001')

              , ('+886-9-1234-0002')

              , ('886-9-1234-0003')

       ) AS Phones(Phone) 

WHERE 1=1

     AND PARSENAME(REPLACE(Phone, '-', '.'), 3) = '9'