在 Oracle 上使用 Regular Expression

最近有個 SQL 的需求, 內容很簡單, 要把某個欄位是 '99', '999', '9999', ..., '9...9999' (最多是30個9) 的資料都 SELECT 出來, 方法其實也很簡單, 就 SELECT * FROM table1 WHERE field1 IN ('99', '999', '9999', .....) 就可以了, 不過問題在於, 類似的條件有三個, 所以寫起來就會變成 IN 裡面有 100 個參數... 當然是可以解決, 不過覺得方法滿 ugly 的. 而且, 印象中, IN 後面參數太多, 會導致 performance 變差. 之前就是有個需求, IN 後面的參數個數不固定, 但又要求一定要用 bind variable, 不能在程式裡面用字串產生 SQL, 於是就寫成 IN (?,?,?,?,...) 固定寫 100 個, 如果查詢個數不到 100 個, 就給空值, 超過 100 個, 就用迴圈. 那時候就有發現 IN 太多, 速度會變慢...

本來第一個反應是如果有 Regular Expression 的話就簡單了, 不過印象中 Oracle 並沒有支援. 但是查了一下, 原來 Oracle 10g 開始就有提供 Regular Expression 了. 目前提供了 4 個 function, 分別是 REGEXP_LIKE, REGEXP_INSTR, REGEXP_SUBSTR 和 REGEXP_REPLACE, 相對於以前的 LIKE, INSTR, SUBSTR 以及 REPLACE.

所以呢, 最後就寫成

YA! 搞定收工

參考文件:
Writing Better SQL Using Regular Expressions, 這份是官方文件, 第一頁是介紹, 第二頁有詳細的參數說明