Oracle中的正則函數

一、Oracle中的正則函數

1.REGEXP_SUBSTR

 (1)函數調用:REGEXP_SUBSTR(source_char,pattern,position,occurrence,match_param,subexpr)

    source_char:被比對的字元串

    pattern:正規表達式

    position:比對開始位置(可選,預設為1)

    occurrence:比對第幾個位置(可選,預設1)

    match_param:區分大小寫檢索(可選,i不分大小寫，c區分大小寫，預設c)

    subexpr:找到正規表達式中的第幾個子表達式

 (2)使用:按正規表達式取字元串

 (3)例子

   1)SELECT REGEXP_SUBSTR('1,2,3,4,5','[^,]+') "REGEXPR_SUBSTR" FROM DUAL;

     結果 REGEXPR_SUBSTR

          1

   2)結合connect by 可以按照某個符号取出一列資料  

     SELECT REGEXP_SUBSTR('1,2,3,4,5','[^,]+',1,LEVEL) "REGEXPR_SUBSTR" FROM DUAL CONNECT BY LEVEL<=5;

   3)字元串中不确定被分割多少個字元串

     SELECT REGEXP_SUBSTR('1,2,3,4,5','[^,]+',1,LEVEL) "REGEXPR_SUBSTR" FROM DUAL CONNECT BY LEVEL<=LENGTH('1,2,3,4,5')-LENGTH(REPLACE('1,2,3,4,5',',',''))+1;   

   4)截取兩個不同字段的字元串為多個子串

    WITH tmp AS

     (SELECT '小明,小紅' s_name, '10,20' s_pay FROM dual

      UNION ALL

      SELECT '小紅,小華,小張', '30,40,80'FROM dual

      UNION ALL

      SELECT '小明', '50'FROM dual)

    SELECT regexp_substr(t.s_name, '[^,]+', 1, l),

           regexp_substr(t.s_pay, '[^,]+', 1, l)

      FROM tmp t,(SELECT LEVEL l FROM dual CONNECT BY LEVEL <= 100)

     WHERE l <=LENGTH(t.s_name) - LENGTH(regexp_replace(t.s_name, ',', '')) + 1;

2.REGEXP_LIKE

    (1)函數調用:REGEXP_LIKE(source_char,pattern,match_param)

       source_char:被比對的字元串

       pattern:正規表達式

       match_param:區分大小寫檢索(可選,i不分大小寫，c區分大小寫，預設c)

    (2)例子

     1)例1(正規表達式的*元字碼)

     WITH tmp AS

      (SELECT 'AD' elem FROM dual

       UNION ALL

       SELECT 'A1D' FROM dual

       UNION ALL

       SELECT 'A09D'FROM dual

       UNION ALL

       SELECT 'A09' FROM dual)

     SELECT * FROM tmp WHERE regexp_like(elem, '[a-z|A-Z][0-9]*[a-z|A-Z]');

     2)例2(正規表達式+元字碼)

     WITH tmp AS

      (SELECT 'AD' elem FROM dual

       UNION ALL

       SELECT 'A1D' FROM dual

       UNION ALL

       SELECT 'A09D'FROM dual

       UNION ALL

       SELECT 'A09' FROM dual)

     SELECT * FROM tmp WHERE regexp_like(elem, '[a-z|A-Z][0-9]+[a-z|A-Z]');

3.REGEXP_INSTR

    (1)函數調用:REGEXP_INSTR (source_string, pattern, position,occurrence,return_option,match_parameter,subexpr)

        source_string：輸入的字元串

        pattern：正規表達式

        position：辨別從第幾個字元開始正規表達式比對。（預設為1）

        occurrence：辨別第幾個比對組。（預設為1）

        return_option：0——傳回第一個字元出現的位置。1：pattern下一個字元起始位置。

        match_parameter：取值範圍

            i：大小寫不敏感；

            c：大小寫敏感；

              n：點号 . 不比對換行符号；

              m：多行模式；

             x：擴充模式，忽略正規表達式中的空白字元。

        subexpr:找到正規表達式中的第幾個子表達式    

    (2)例子

     1)例1

     SELECT regexp_instr('正規表達式在Oracle11g中的應用', '[a-z|A-Z|0-1]+')FROM dual;

     2)例2(occurrence)

     SELECT regexp_instr('正規表達式在Oracle11g中的應用oralce', '[a-z|A-Z|0-1]+',1,2)FROM dual;

     3)例3(return_option)

     SELECT regexp_instr('正規表達式在Oracle11g中的應用', '[a-z|A-Z|0-1]+',1,1,1) FROM dual;

     4)SELECT regexp_instr('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 4)  FROM DUAL;

4.REGEXP_REPLACE

    (1)函數調用

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

        source_string：輸入的字元串

        pattern：正規表達式

        position：辨別從第幾個字元開始正規表達式比對。（預設為1）

        occurrence：辨別第幾個比對組。（預設為1）

        match_parameter：取值範圍

    (2)例子    

     1)例1(替換字元串位置)

     SELECT regexp_replace('Ellen Hildi Smith', '(.*) (.*) (.*)', '\3, \1 \2') FROM dual