2014年5月10日 星期六

剔除前置0(左側)字元

本範例將探討如何將『00298』之前置0剔除為『298』。
MSSQL可使用型態轉換』(不建議使用)擷取局部字串方法兩種方法;ORACLE可直接使用TRIM函數即可。

MSSQL
可使用型態轉換擷取局部字串方法兩種方法,SQL及執行結果說明如下
SQL
執行結果
SELECT Id
--Method 1: 建議勿用
, CAST( CAST(Id AS INT) AS VARCHAR)           "Mthd 1"
--Method 2:
, SUBSTRING(Id, PATINDEX('%[^0]%', Id), 8000) "Mthd 2"
, PATINDEX('%[^0]%', Id)                      Pos
FROM
     (
SELECT '00001' Id
UNION ALL
SELECT '00298'
UNION ALL
SELECT '05123'
--UNION ALL
--SELECT '00ABC'
   ) A
Method-1:型態轉換
應用數值型態可將前置0(左側)將自動捨去(無存在意義)的特性進行剔除,首先以CAST(Id AS INT)文字轉換為數值型態,即可將前置0剔除,再用CAST轉換回原文字型態。由於應用型態轉換,因此當具有非數字字元時,將發生型態轉換錯誤

Method-2:擷取局部字串
欲將前置0剔除,可使用SUBSTRING函數以擷取自第1碼非0字元直至字串結束為止,第1碼非0字元位置可應用PATINDEX函數, PATINDEX函數可利用字串的特徵(Pattern)以找出1個字元不為0之位置,此函數運用類似正則表示式(Regular Expression)進行目標字串搜尋,常用於數值驗證、文字驗證,以身分證字等。PATINDEX語法如下:
PATINDEX('%pattern%,  string)

PATINDEX('%[^0]%', Id)找出第一個0字元位置,而擷取長度則可直接使用8000,可省去長度計算之成本,應用想法可參閱拙著,如何減少非必要運算也是效能調校之重要課題。

ORACLE
可直接使用TRIM函數,TRIM()函數除可應用於較廣為人知的移除前後空白外,也可用來移除前後的特定字元,語法如下:
TRIM([TRAILING|LEADING|BOTH] trimstring FROM string)

應用此函數之SQL及執行結果如下所示:
SQL
執行結果
SELECT Id
      , TRIM(LEADING '0' FROM Id)  "LEADING"
FROM
  (
     SELECT '00298' Id
     FROM DUAL
     UNION ALL
     SELECT '05123'
     FROM DUAL
     UNION ALL
     SELECT '00ABC'
     FROM DUAL
    )


此篇文章探討如何剔除前置0,反之如欲前置補0至特定長度,則可參考另一篇文章前置補0至固定長度

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'