剔除地址中全形數字字元

剔除地址中全型數字字元最直接作法即可使用REPLACE函數，但需採用呼叫函數10次，或近似迴圈想法才可解決，以下介紹TRANSLATE函數及REGEXP_REPLACE函數，新版MSSQL已提供TRANSLATE函數，MSSQL使用者可嘗試使用。

1.  TRANSLATE函數

此函數可使用字元對應轉換（即翻譯），此函數將依照轉換(string_to_replace:replacement_string)定義(N:1)進行轉換，如無可對應者則轉為空字串，請特別注意replacement_string不得為空字串，否則將轉為NULL。範例中也展示如何將全形數字轉為半形數字。

TRANSLATE( string1, string_to_replace, replacement_string )

2.  REGEXP_REPLACE函數

REGEXP_REPLACE(source_string                 , pattern                [, replace_string [, position [,occurrence, [match_parameter]]]]                )

SELECT Addr

       , TRANSLATE(Addr, '@０１２３４５６７８９', '@')

       , TRANSLATE(Addr, '@０１２３４５６７８９', '@0123456789')      

       , REGEXP_REPLACE(Addr, '[０１２３４５６７８９]','')

FROM

    (      

    SELECT '１００９０台北市中正區羅斯福路９段１號３樓' Addr

    FROM DUAL

    UNION ALL

    SELECT '１００台北市中正區幸忠孝東路９段１９號４樓'

    FROM DUAL

    UNION ALL

    SELECT '台北市中正區忠孝東路５段４９號'

    FROM DUAL

     ) 

[MSSQL] 如何簡便快速將[日期]、[時間]兩欄位合併轉換為日期型態

許多設計偏好將[日期型態]資料拆分成[日期]、[時間]兩個欄位資料（字串或數值型態），對於ORACLE而言，可直接使用TO_DATE函數進行各態樣的日期字串的轉換成日期型態。MSSQL常透過CAST進行日期轉換，但需符合兩項重點: 1.日期、時間中間需含空白及2. 時間間隔符號(:)，因此需先處理以符合轉換格式，也可使用本範例所介紹agent_datetime內建函數，簡化煩瑣處理步驟。 

SELECT TRADE_DATE, TRADE_TIME      --方法1: 一般作法 (需先處理符合日期轉換格)      , CAST(E.TRADE_DATE + ' '             + SUBSTRING(E.TRADE_TIME, 1, 2) + ':'             + SUBSTRING(E.TRADE_TIME, 3, 2) + ':'             + SUBSTRING(E.TRADE_TIME, 5, 2)                  AS DATETIME             ) TR_DT      ----      --方法2: 使用agent_datetime函數      , msdb.dbo.agent_datetime(TRADE_DATE, TRADE_TIME) TR_DT2 FROM          (     --嘗試轉型為數字     --SELECT 20180112 TRADE_DATE,    081500 TRADE_TIME--重要範例     --UNION ALL     SELECT '20180112' TRADE_DATE, '081500' TRADE_TIME     UNION ALL     SELECT '20180112' TRADE_DATE, '131500' TRADE_TIME     UNION ALL       SELECT '20180112' TRADE_DATE, '131530' TRADE_TIME     ) E

將[日期型態]資料拆分成[日期]、[時間]兩個文字或數值型態的欄位資料，雖可簡化純指令式的Ad hoc查詢處理，但後續如需日期運算則內建函數無法直接使用，需先行轉態。本範例所介紹agent_datetime內建函數可輕鬆完成完成換程序，如使用數值型態方式時，則更可凸顯其方便性，以早上08:15:00如以數值型態儲存時則變為081500，當轉換時，更需額外處理補足碼問題。

找出最後一碼為小寫字母之資料

MSSQL

方法(1)：用ASCII函數取得小寫字母值域。

方法(2)：LIKE指令，搜尋中括號所列任一之小寫字母。

方法(3)：PATINDEX，與LIKE雷同。

SELECT Crncy

, RIGHT(Crncy, 1)

, CASE WHEN ASCII(RIGHT(Crncy, 1)) BETWEEN 96 AND 123 THEN 1 ELSE 0 END        "M-1"

, CASE WHEN Crncy COLLATE Latin1_General_CS_AI LIKE '%[abcdefghijklmnopqrestuvwxyz]'

THEN 1 ELSE 0 END "M-2"

, PATINDEX('%[abcdefghijklmnopqrestuvwxyz]%', Crncy COLLATE Latin1_General_CS_AI) "M-3" 

FROM

    (VALUES('UsD'), ('EUR'), ('BRL'), ('GBp')) AS Ex(Crncy)

ORACLE

方法(1)：用ASCII函數取得小寫字母值域。

方法(2)：REGEXP_INSTR

SELECT COLUMN_VALUE

   , SUBSTR(COLUMN_VALUE, -1) BACKEND

   , CASE WHEN ASCII(SUBSTR(COLUMN_VALUE, -1)) BETWEEN 97 AND 122 THEN 1 ELSE 0 END "M-1"

   , REGEXP_INSTR(COLUMN_VALUE, '[[:lower:]]$') "M-2a"

   , REGEXP_INSTR(COLUMN_VALUE, '[a-z]$') "M-2b"

FROM TABLE(SPLIT_TBL('UsD','EUR','BRL','GBp'))

註: CREATE OR REPLACE TYPE SPLIT_TBL AS TABLE OF VARCHAR2(32767)

找出欄位值中最小(大)值

ORACLE直接使用LEAST函數(最小)或GREATEST函數(最大)即可，但MSSQL目前版本尚未提供類似函數，但可用CROSS APPLY/VALUES等組合出所需功能。

	ORACLE	MSSQL
資料	CREATE TABLE T ( ID INT , Col1  INT, COL2  INT, COL3  INT )  INSERT INTO T VALUES(1, 1, 2, 3); INSERT INTO T VALUES(2, 5, 8, 4); INSERT INTO T VALUES(3, 7, 2, 6);	CREATE TABLE #T ( ID INT IDENTITY(1, 1), Col1  INT, COL2  INT, COL3  INT )   INSERT INTO #T VALUES(1, 2, 3) INSERT INTO #T VALUES(5, 8, 4) INSERT INTO #T VALUES(7, 2, 6)
SQL	SELECT ID, Col1, Col2, Col3   , LEAST(COL1, COL2, COL3) MinVal   , GREATEST(COL1, COL2, COL3) MaxVal FROM T	SELECT ID, Col1, Col2, Col3      , MinVal      , MaxVal FROM #T CROSS APPLY   (    SELECT MIN(d) MinVal         , MAX(d) MaxVal    FROM (VALUES (Col1),(Col2),(Col3)) AS a(d)   ) A (1). 以VALUES函數，逐筆將Col1~3轉成僅具1個欄位      (d)的虛擬Table(a)，原3個欄位轉成3筆資料。 (2). 再以MIN/MAX函數找出3筆中最小(大)值      (即原資料中3個欄位中最小(大)值)。

如何取得本月第三個週三 (期貨轉倉日)

每月的第三個週三為期貨轉倉日，以下範例將進行說明，將使用以下二個重要概念：

1.   取得下一個週三

請注意下週三與下一個週三意義不同，如今天為週二，則下週三為第8天，而下一個週三則為次日。ORACLE可直接使用NEXT_DAY函數，MSSQL無對應函數，但可使用計算方式求得。

2.   使用上個月底日為基準日

本月第三個週三，如使用本月初為基準日，需額外判斷月初是否符合第一個週三等煩雜運算邏輯，如使用上個月月底日，即可完全省略此判斷式（重點）。

DB	SQL
ORACLE	SELECT NEXT_DAY(TRUNC(DATE'2018-01-02', 'MM')-1, 4) + 2*7 "第三個週三" FROM DUAL 上個月月底: TRUNC(DATE'2018-01-02', 'MM')-1 下一個週三: NEXT_DAY({DATE}, 4)
MSSQL	SELECT Tgt   , CONVERT(CHAR(10)       , Tgt + (7- DATEPART(DW, Tgt) + 3)%7+1       , 120) "下一個週三"   , DATEADD(DD, -1, CONVERT(CHAR(8), Tgt, 120)+'01')  "上個月底日"      , DATEADD(DD, -1, CONVERT(CHAR(8), Tgt, 120)+'01')               + (7          - DATEPART(DW             , DATEADD(DD, -1, CONVERT(CHAR(8), Tgt, 120)+'01')) + 3)%7 +1     + 2*7 "第三個週三" FROM    (    SELECT CAST('2018-01-02' AS DATETIME) Tgt    --UNION ALL    --SELECT CAST('2018-03-30' AS DATETIME)    ) A 上個月月底: DATEADD(DD, -1, CONVERT(CHAR(8), Tgt, 120)+'01') 下一個週三: Tgt + (7- DATEPART(DW, Tgt) + 3)%7 +1