在Oracle中用正規式換算欄位值長度為Bytes

2012-12-21

2122
0

Dotblogs 的標籤：隨手記 ,Oracle ,Regex

算是沿續上一篇LINQ to Entities (Oracle) 中如何使用正規式的內容，角度回到 Oracle 身上。

上一篇把資料倒回記憶體中，再用 .Net 正規運算式，把地址欄位長度換算成 bytes，這算是不得已的方式，因為畢竟會有嚴重的效能問題，所以若是想在 Oracle 中，直接計算出欄位值的長度，而且以 byte 為單位，該怎麼寫？

首先當然要了解 Oracle 支援的正規運算式的格式和內容，可以參閱以下文章：

Using Regular Expressions in Oracle Database

Oracle Database Regular Expression Support

Oracle Regular Expressions

我也是爬完上面的文章後，一開始先試了 Regexp_Instr，結果得不到我要的結果，後來改用 Regexp_Replace 的方式，終於找出解法：


       SELECT T.ID, T.V2ADDRESS, 
        (
          (LENGTH(T.V2ADDRESS) * 2) -
           NVL(LENGTH(REGEXP_REPLACE(T.V2ADDRESS, '[^\x00-\xff]+', '', 1, 0)), 0) - 
           NVL(LENGTH(REGEXP_REPLACE(T.V2ADDRESS, '[^\(\)\.\\\/,:;\-\_\=\+\*\s]+', '', 1, 0)), 0)
         ) AS BYTELENGTH
       --,REGEXP_REPLACE(T.V2ADDRESS, '[^\(\)\.\\\/,:;\-\_\=\+\*\s]+', '', 1, 0) AS REG_STR
       FROM LEO_TEST T
       WHERE T.V2ADDRESS IS NOT NULL
         AND LENGTH(T.V2ADDRESS) > 40
) SQ
WHERE SQ.BYTELENGTH > 80
ORDER BY BYTELENGTH DESC;

153129

要特別注意，為什麼我要另外寫一組正規式：[^\(\)\.\\\/,:;\-\_\=\+\*\s]+ 呢？原因是，在我的 11g 資料庫中，會把半形的標點符號之編碼放在非標準 ASCII 區域，造成長度誤判，所以只好再加第二個正規式把這些半形標點符號抓出來。

簡單說明一下上述的計算方式，就是先把欄位長度 * 2，也就是把所有字元都當做雙位元字元，然後再把半形的數量扣掉。但是 Oracle 的 Regex_Replace 若找不到可以替換的來源字串，最後會回傳 Null 值，結成運算結果出錯，所以要再多包一層 NVL。

--------
沒什麼特別的~
不過是一些筆記而已

回首頁