Oracle10g - The regular expression

Jun 30 posted in Oracle (RSS), 98 views.

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 --