剔除前置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至固定長度》。

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