Oracle10g - The regular expression
- 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 -- |
Related Posts of Oracle10g - The regular expression
-
RMAN conducted through the bare equipment and file system data file transfer between
Easy to be an example to introduce how to use data files Rman bare equipment and file system conversion. 1. EYGLE set up equipment used to test bare table space 2. Use RMAN to back up the COPY function of the bare device file for the file system file ...
-
How to have the archive log restore a lost data files
Kamus yesterday to ask a question, if a cold backup, but missing one of a data file, but the archive of all existence, it should be how to restore data files. Try yourself, is perhaps the following steps: [oracle @ jumper eygle] $ sqlplus "/ as ...
-
Oracle HowTo: a die to determine the progress of affairs Recovery
Encountered a problem yesterday, at the opening of the serial SMON is restored, for a die affairs, how to observe the progress of their recovery. Service has been unable to die because of v $ transaction through to observe, so must be carried out to ...
-
Oracle HowTo: determine whether a resumption of the progress of death Affairs
Encountered a problem yesterday in the opening of the serial SMON is restored, for a death matters, how to observe the progress of their recovery. Service has been unable to die because of v $ transaction through to observe, so must be carried out to ...
-
UNDO table space reconstruction
$ Sqlplus / nolog SQL> connect / as sysdba SQL> create undo tablespace UNDOTBS2 datafile '/ ora_data/UNDOTBS02.DBF' size 1M extent management local; SQL> alter system set undo_tablespace = UNDOTBS2; SQL> drop tablespace UNDOTBS1 includ
-
Oracle Sysdba certification authority management
<br /> Outlined one at ORACLE has a special level of competence - sysdba authority, sysdba privileges ORACLE systems have the highest authority, has opened the database, turn off the database, restore database, such as advanced permissions, the ...
-
Oracle Database exp imp Import Export by user examples
◆ 1. From one windows server A on Test1 export all objects in the user, and then into the linux server B on Test2 users. (Test1 known password for the system users Test1passwd or export is required) ◆ 2.B user Test2 machine does not exist, or Test2 b ...
-
Oracle10g ASM database table space maintenance
1. Default naming Oracle when using ASM + OMF management, Oracle distribution file name will automatically create the appropriate file. Our job to create table space can be simplified as follows: A command. The following are the default Oracle naming ...
-
Merge the use of (1)
Merge to select from a table update or insert some data to another table. And finally is used to update or insert depends on the conditions of the statement. rn Here's our easy to cite an example: rn rn SQL> create table merge_test1 (a number, ...
-
Dbms_obfuscation_toolkit use of Oracle's encryption and decryption of data (to)
In order to protect sensitive data, oracle start from 8i to provide a data encryption package: dbms_obfuscation_toolkit. Take advantage of this package, our data can be DES, Triple DES or MD5 encryption. This article on the use of this and the use of ...













Leave a Reply