2014年5月4日 星期日

[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)


以下將應用於截取第34段子網段,第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' 


沒有留言:

張貼留言