Oracle10g - The regular expression
On the basis of regular expressions that can refer to "layman's language of regular expressions", Oracle10g achieved with the regular expression is basically the same, the details can refer to "10g in regular expressions." Oracle10g provides four regular expressions can use the function:- regexp_like
- regexp_substr
- regexp_instr
- regexp_replace
Sys.standard in the package can be seen from these functions (see appendix). Here the main function of these four to do some small tests.
1. Regexp_like
REGEXP_LIKE (srcstr, pattern, modifier)
__srcstr: Search String
__pattern: matching model
__modifier: Search mode ( 'i' is not case-sensitive search; 'c' is case-sensitive search. The default is' c '.)
__return type: boolean
eg:
| SQL> select 1 from dual where regexp_like ( 'Alibaba-13930471222', '^ alibaba-[139 | 135 | 152] [0-9] + (8 }$'); 1 ---------- SQL> select 1 from dual where regexp_like ( 'Alibaba-13930471222', '^ alibaba-[139 | 135 | 152] [0-9] + (8) $', 'c'); 1 ---------- SQL> select 1 from dual where regexp_like ( 'Alibaba-13930471222', '^ alibaba-[139 | 135 | 152] [0-9] + (8) $', 'i'); 1 ---------- 1 |
2. Regexp_substr
REGEXP_SUBSTR (srcstr, pattern, position, occurrence, modifier)
__srcstr: Search String
__pattern: matching model
__position: Search srcstr starting position (default is 1)
__occurrence: search the first matching pattern appears several times in the string (defaults to 1)
__modifier: Search mode ( 'i' is not case-sensitive search; 'c' is case-sensitive search. The default is' c '.)
eg:
| SQL> select regexp_substr ( 'http://blog.chinaunix.net/u/30637/showart_1009927.html', '[0-9] +') from dual; REGEXP_SUBSTR ( 'HTTP: / / BLOG.CHI ------------------------------ 30637 SQL> select regexp_substr ( 'http://blog.chinaunix.net/u/30637/showart_1009927.html', '[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://blog.chinaunix.net/u/30637/Showart_1009927.html', '[a-z_0-9] +', 35) from dual; REGEXP_SUBSTR ( 'HTTP: / / BLOG.CHI ------------------------------ howart_1009927 SQL> select regexp_substr ( 'http://blog.chinaunix.net/u/30637/Showart_1009927.html', '[a-z_0-9] +', 35, 1, 'c') from dual; REGEXP_SUBSTR ( 'HTTP: / / BLOG.CHI ------------------------------ howart_1009927 SQL> select regexp_substr ( 'http://blog.chinaunix.net/u/30637/Showart_1009927.html', '[a-z_0-9] +', 35, 1, 'i') from dual; REGEXP_SUBSTR ( 'HTTP: / / BLOG.CHI ------------------------------ Showart_1009927 |
3. Regexp_instr
REGEXP_INSTR (srcstr, pattern, position, occurrence, returnparam, modifier)
__srcstr: Search String
__pattern: matching model
__position: Search srcstr starting position (default is 1)
__occurrence: search the first matching pattern appears several times in the string (defaults to 1)
__returnparam: return to the substring position in srcstr (0 that the first position, a position that the end of +1, the default is 0.)
__modifier: Search mode ( 'i' is not case-sensitive search; 'c' is case-sensitive search. The default 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://shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[0-9] +', 21) from dual; REGEXP_INSTR ( 'HTTP: / / SHOP35741 ------------------------------ 52 SQL> select regexp_instr ( 'http://shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[0-9] +', 1, 2) from dual; REGEXP_INSTR ( 'HTTP: / / SHOP35741 ------------------------------ 52 SQL> select regexp_instr ( 'http://shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[0-9] +', 1, 1, 0) from dual; REGEXP_INSTR ( 'HTTP: / / SHOP35741 ------------------------------ 12 SQL> select regexp_instr ( 'http://shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[0-9] +', 1, 1, 1) from dual; REGEXP_INSTR ( 'HTTP: / / SHOP35741 ------------------------------ 20 SQL> select regexp_instr ( 'http://shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[0-9] +', 1, 2, 0) from dual; REGEXP_INSTR ( 'HTTP: / / SHOP35741 ------------------------------ 52 SQL> select regexp_instr ( 'http://shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[0-9] +', 1, 2, 1) from dual; REGEXP_INSTR ( 'HTTP: / / SHOP35741 ------------------------------ 60 SQL> select regexp_instr ( 'http://shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[az] * [0-9] +', 1, 1, 0) from dual; REGEXP_INSTR ( 'HTTP: / / SHOP35741 ------------------------------ 8 SQL> select regexp_instr ( 'http://Shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[az] * [0-9] +', 1, 1, 0) from dual; REGEXP_INSTR ( 'HTTP: / / SHOP35741 ------------------------------ 9 SQL> select regexp_instr ( 'http://Shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[az] * [0-9] +', 1, 1, 0, 'i') from dual; REGEXP_INSTR ( 'HTTP: / / SHOP35741 ------------------------------ 8 SQL> select regexp_instr ( 'http://Shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[az] * [0-9] +', 1, 1, 0, 'c') from dual; REGEXP_INSTR ( 'HTTP: / / SHOP35741 ------------------------------ 9 |
4. Regexp_replace
function REGEXP_REPLACE (srcstr, pattern, replacestr, position, occurrence, modifier)
__srcstr: Search String
__pattern: matching model
__replacestr: new substring (default is NULL)
__position: srcstr retrieval start position (default is 1)
__occurrence: there several times to replace the first string match pattern (default is 0)
__modifier: Search mode ( 'i' is not case-sensitive search; 'c' is case-sensitive search. The default is' c '.)
| SQL> select regexp_replace ( 'http://Shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[az] * [0-9] +', 'YCT') from dual; REGEXP_REPLACE ( 'HTTP: / / SHOP357 -------------------------------------------------- http://SYCT.taobao.com/shop/xshop/wui_page-YCT.htm SQL> select regexp_replace ( 'http://Shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[az] * [0-9] +', 'YCT', 20) from dual; REGEXP_REPLACE ( 'HTTP: / / SHOP357 -------------------------------------------------- -------- http://Shop35741645.taobao.com/shop/xshop/wui_page-YCT.htm SQL> select regexp_replace ( 'http://Shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[az] * [0-9] +', 'YCT', 1, 2) from dual ; REGEXP_REPLACE ( 'HTTP: / / SHOP357 -------------------------------------------------- -------- http://Shop35741645.taobao.com/shop/xshop/wui_page-YCT.htm SQL> select regexp_replace ( 'http://Shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[az] * [0-9] +', 'YCT', 1, 1) from dual ; REGEXP_REPLACE ( 'HTTP: / / SHOP357 -------------------------------------------------- ----- http://SYCT.taobao.com/shop/xshop/wui_page-16115021.htm SQL> select regexp_replace ( 'http://Shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[az] * [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://Shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[az] * ([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://Shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[az] * ([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 FIPSFLAG ( '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, occurrence PLS_INTEGER: = 1, returnparam PLS_INTEGER: = 0, modifier VARCHAR2 DEFAULT NULL) return PLS_INTEGER; pragma FIPSFLAG ( '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, occurrence PLS_INTEGER: = 1, modifier VARCHAR2 DEFAULT NULL) return VARCHAR2 CHARACTER SET srcstr% CHARSET; pragma FIPSFLAG ( '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); - REGEXP_REPLACE -- function REGEXP_REPLACE (srcstr VARCHAR2 CHARACTER SET ANY_CS, pattern VARCHAR2 CHARACTER SET srcstr% CHARSET, replacestr VARCHAR2 CHARACTER SET srcstr% CHARSET DEFAULT NULL, position PLS_INTEGER: = 1, occurrence 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 -- |
Tags: pragma FIPSFLAG() sql, substr, several times, appendix, occurrence, sensitive search, model position, string pattern, regular expression, regexp, position search, alibaba, search string, search mode, instr, layman, regular expressions
Permalink: http://www.kods.netwww.kods.net/oracle10g-the-regular-expression/
















