For a basic introduction to regular expressions, refer to deep dive regular expressions. The regular expressions implemented in Oracle10g are basically the same. For details, refer to regular expressions in 10g. Oracle10g provides four functions that can use regular expressions:
- Regexp_like
- Regexp_substr
- Regexp_instr
- Regexp_replace
The declaration of these functions can be seen in the SYS. Standard package (see the appendix ). Here we mainly perform some minor tests on these four functions.1. regexp_likeRegexp_like (srcstr, pattern, modifier) _ srcstr: search string _ pattern: Match pattern _ modifier: search mode ('I' is case insensitive for search; 'C' is case sensitive for retrieval. The default value is 'C '.) _ Return type: Boolean eg:
| SQL> select 1 from dual where regexp_like ('alibabaca-000000', '^ alibabaca-[ 13930471222 | 139 | 135] [0-9] + {8} $ '); 1 ---------- SQL> select 1 from dual where regexp_like ('alibabaca-000000', '^ Alibaba-[13930471222 | 139 | 135] [0-9] + {8} $ ', 'C'); 1 ---------- SQL> select 1 from dual where regexp_like ('alibabaca-123456 ', '^ Alibaba-[139 | 135 | 152] [0-9] + {8} $', 'I'); 1 ---------- 1 |
2. regexp_substrRegexp_substr (srcstr, pattern, position, occurrence, modifier) _ srcstr: search string _ pattern: Match pattern _ Position: Start position of srcstr (1 by default) _ occurrence: searches for strings with matching modes for the first time (default value: 1) _ modifier: search mode ('I' is case-insensitive for search; 'C' is case sensitive for retrieval. The default value is 'C '.) Eg:
| SQL> select regexp_substr ('HTTP: // logs, '[0-9] +') from dual; regexp_substr ('HTTP: // BLOG.CHI------------------------------30637 SQL> select regexp_substr ('HTTP: // outputs, '[0-9] +', 1) from dual; regexp_substr ('HTTP: // BLOG.CHI------------------------------30637 SQL> select regexp_substr ('HTTP: // blog.chinaunix.net/u/30637/showart_1009927.html ', '[0-9] +', 35) from dual; regexp_substr ('HTTP: // BLOG.CHI------------------------------1009927 SQL> select regexp_substr ('HTTP: // blog.chinaunix.net/u/30637/showart_1009927.html ', '[0-9] +', 1, 2) from dual; regexp_substr ('HTTP: // BLOG.CHI------------------------------1009927 SQL> select regexp_substr ('HTTP: // blog.chinaunix.net/u/30637/showart_1009927.html ', [a-z_0-9] + ', 35) from dual; regexp_substr ('HTTP: // blog. chi ------------------------------ showart_1009927 SQL> select regexp_substr ('HTTP: // done, '[a-z_0-9] +', 35) from dual; regexp_substr ('HTTP: // blog. chi ---------------------------- howart_1009927 SQL> select regexp_substr ('HTTP: // done, '[a-z_0-9] +', 35, 1, 'C') from dual; regexp_substr ('HTTP: // blog. chi ---------------------------- howart_1009927 SQL> select regexp_substr ('HTTP: // response, '[a-z_0-9] +', 35, 1, 'I') from dual; regexp_substr ('HTTP: // blog. chi -------------------------------- showart_1009927 |
3. regexp_instrRegexp_instr (srcstr, pattern, position, occurrence, returnparam, modifier) _ srcstr: search string _ pattern: Match pattern _ Position: Search srcstr start position (1 by default) _ occurrence: searches for the number of matching strings (1 by default) _ returnparam: returns the position of the substring in srcstr (0 indicates the header position, 1 indicates the tail position + 1. The default value is 0 .) _ Modifier: search mode ('I' is case-insensitive and 'C' is case-sensitive. The default value is 'C '.)
| SQL> select regexp_instr ('HTTP: // shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[0-9] +') from dual; regexp_instr ('HTTP: // shop35741 ---------------------------- 12 SQL> select regexp_instr ('HTTP: // success, '[0-9] +', 21) from dual; regexp_instr ('HTTP: // shop35741 ---------------------------- 52 SQL> select regexp_instr ('HTTP: // success, '[0-9] +', 1, 2) from dual; regexp_instr ('HTTP: // shop35741 ---------------------------- 52 SQL> select regexp_instr ('HTTP: // success, '[0-9] +', 1, 1, 0) from dual; regexp_instr ('HTTP: // shop35741 ---------------------------- 12 SQL> select regexp_instr ('HTTP: // success, '[0-9] +', 1, 1, 1) from dual; regexp_instr ('HTTP: // shop35741 ---------------------------- 20 SQL> select regexp_instr ('HTTP: // success, '[0-9] +', 1, 2, 0) from dual; regexp_instr ('HTTP: // shop35741 ---------------------------- 52 SQL> select regexp_instr ('HTTP: // success, '[0-9] +', 1, 2, 1) from dual; regexp_instr ('HTTP: // shop35741 ------------------------------ 60 SQL> select regexp_instr ('HTTP: // success, '[A-Z] * [0-9] +', 1, 1, 0) from dual; regexp_instr ('HTTP: // shop35741 -------------------------------- 8 SQL> select regexp_instr ('HTTP: // region, '[A-Z] * [0-9] + ', 1, 1, 0) from dual; regexp_instr ('HTTP: // shop35741 -------------------------------- 9 SQL> select regexp_instr ('HTTP: // region ', '[A-Z] * [0-9] +', 1, 1, 0, 'I') from dual; regexp_instr ('HTTP: // shop35741 ---------------------------- 8 SQL> select regexp_instr ('HTTP: // success, '[A-Z] * [0-9] +', 1, 1, 0, 'C') from dual; regexp_instr ('HTTP: // shop35741 -------------------------------- 9 |
4. regexp_replaceFunction regexp_replace (srcstr, pattern, replacestr, position, occurrence, modifier) _ srcstr: search string _ pattern: Match pattern _ replacestr: New substring (null by default) _ Position: Start position of the srcstr search (1 by default) _ occurrence: replace the number of matching strings (0 by default) _ modifier: retrieval mode ('I' is case-insensitive for retrieval; 'C' is case-sensitive for retrieval. The default value is 'C '.)
| SQL> select regexp_replace ('HTTP: // region, '[A-Z] * [0-9] +', 'yct ') from dual; regexp_replace ('HTTP: // shop357 ------------------------------------------------ http://SYCT.taobao.com/shop/xshop/wui_page-YCT.htm SQL> select regexp_replace ('HTTP: // rewrite, '[A-Z] * [0-9] +', 'yct ', 20) from dual; regexp_replace ('HTTP: // shop357 explain http://Shop35741645.taobao.com/shop/xshop/wui_page-YCT.htm SQL> select regexp_replace ('HTTP: // replace, '[A-Z] * [0-9] + ', 'yct ', 1, 2) from dual; regexp_replace ('HTTP: // shop357 explain http://Shop35741645.taobao.com/shop/xshop/wui_page-YCT.htm SQL> select regexp_replace ('HTTP: // configure ', '[A-Z] * [0-9] +', 'yct ', 1, 1) from dual; regexp_replace ('HTTP: // shop357 explain http://SYCT.taobao.com/shop/xshop/wui_page-16115021.htm SQL> select regexp_replace ('HTTP: // delimiter, '[A-Z] * [0-9] +', 'yct ', 1, 1, 'I') from dual; regexp_replace ('HTTP: // shop357 ------------------------------------------------------ http://YCT.taobao.com/shop/xshop/wui_page-16115021.htm SQL> select regexp_replace ('HTTP: // replace ', '[A-Z] * ([0-9]) +', '/1', 1, 1,' I ') from dual; regexp_replace ('HTTP: // shop357 -------------------------------------------------- http://5.taobao.com/shop/xshop/wui_page-16115021.htm SQL> select regexp_replace ('HTTP: // rewrite, '[A-Z] * ([0-9] +)', '/1 ', 1, 1, 'I') from dual; regexp_replace ('HTTP: // shop357 --------------------------------------------------------------- http://35741645.taobao.com/shop/xshop/wui_page-16115021.htm |
5. Appendix
| -- Regexp_like -- function regexp_like (srcstr varchar2 Character Set any_cs, pattern varchar2 Character Set srcstr % charset, modifier varchar2 default null) return Boolean; Pragma trim ('regexp _ like ', 1452 ); function regexp_like (srcstr clob Character Set any_cs, pattern varchar2 Character Set srcstr % charset, modifier varchar2 default null) return Boolean; Pragma fipsflag ('regexp _ like', 1452 ); -- regexp_instr -- function regexp_instr (srcstr varchar2 Character Set any_cs, pattern varchar2 Character Set srcstr % charset, position pls_integer: = 1, required pls_integer: = 1, required pls_integer: = 0, modifier varchar2 default null) return pls_integer; Pragma evaluate ('regexp _ instr', 1452); function regexp_instr (srcstr clob Character Set any_cs, pattern varchar2 Character Set srcstr % charset, position INTEGER: = 1, occurrence INTEGER: = 1, returnparam pls_integer: = 0, modifier varchar2 default null) return integer; Pragma fipsflag ('regexp _ instr', 1452 ); -- regexp_substr -- function regexp_substr (srcstr varchar2 Character Set any_cs, pattern varchar2 Character Set srcstr % charset, position pls_integer: = 1, required pls_integer: = 1, modifier varchar2 default null) return varchar2 Character Set srcstr % charset; Pragma substring ('regexp _ substr', 1452); function regexp_substr (srcstr clob Character Set any_cs, pattern varchar2 Character Set srcstr % charset, position integer: = 1, occurrence INTEGER: = 1, modifier varchar2 default null) return clob Character Set srcstr % charset; Pragma fipsflag ('regexp _ substr ', 1452 ); -- argument -- function substring (srcstr varchar2 Character Set any_cs, pattern varchar2 Character Set srcstr % charset, replacestr varchar2 Character Set srcstr % charset default null, position pls_integer: = 1, direction pls_integer: = 0, modifier varchar2 default null) return varchar2 Character Set srcstr % charset; Pragma fipsflag ('regexp _ replace ', 1452); function regexp_replace (srcstr clob Character Set any_cs, pattern varchar2 Character Set srcstr % charset, replacestr clob Character Set srcstr % charset default null, position INTEGER: = 1, occurrence INTEGER: = 0, modifier varchar2 default null) return clob Character Set srcstr % charset; Pragma fipsflag ('regexp _ replace', 1452); function regexp_replace (srcstr clob Character Set any_cs, pattern varchar2 Character Set srcstr % charset, replacestr varchar2 Character Set srcstr % charset default null, position INTEGER: = 1, occurrence INTEGER: = 0, modifier varchar2 default null) return clob Character Set srcstr % charset; pragma fipsflag ('regexp _ replace ', 1452); -- end Regexp support -- |
|
|
|
|
