2018年1月31日 星期三

前置補0至固定長度

由於資料擴增而必需原代碼進行升碼處理,以下將說明如何將原代碼(長度3~5碼)前置補『0』為固定為5碼。
ORACLE可直接使用LPAD左側字串填補函數,將代碼左方以『0』補足至5碼。MSSQL無對應函數,但可先將所有代碼前(左)方補上兩碼『0』,再以取出字串右側子字串的RIGHT函數,取出右側5碼,即可捨去超過5碼之左側『0』字元,SQL如下:
資料庫
SQL
說明
MSSQL
SELECT Emp_Id
, '00' + Emp_Id             New_Id
     , RIGHT('00' + Emp_Id, 5) Emp_No
FROM (VALUES('298')
, ('1298')
, ('51298')
) AS Emp(Emp_Id)  
1.  代碼左方補上兩碼『0
2.  由右側取出5
ORACLE
SELECT COLUMN_VALUE Emp_Id
      , LPAD(COLUMN_VALUE, 5, '0') Emp_No
FROM TABLE(SYS.DBMS_DEBUG_VC2COLL('298'
       , '1298'
       , '51298')
      )
LPAD左側字串填補函數,將代碼左方以『0』補足至5

以上範例代碼為字元型態,如為數值型態,ORACLE仍可正常運行;但前述SQL中,MSSQL是使用數學運算符號加號『+』將字串連結,但系統判定轉型為數值,而發生非預期結果,因此需先進行轉型處理,如2012以上版本即可使用CONCAT字串連結函數,如下:
SELECT Emp_Id
     , RIGHT(CONCAT('00', Emp_Id), 5)             "Emp_No(CONCAT)"
        , RIGHT('00'+ CAST(Emp_Id AS VARCHAR(5)), 5) "Emp_No(轉型)"
FROM (VALUES(298)
, (1298)
, (51298)
) AS Emp(Emp_Id) 

如欲進行逆向處理將前置0』剔除,可參考剔除前置0(左側)字元》文章所述。本範中所使用SYS.DBMS_DEBUG_VC2COLL函數為ORACLE未公開指令,使用方法或其他作法可參考另一篇文章CSV String 2 Row(Table)》。

沒有留言:

張貼留言