4 new functions has been introduced:-REGEXP_LIKEREGEXP_REPLACEREGEXP_INSTRREGEXP_SUBSTR
The following data types are supported with REGEXP functions:- CHAR- VARCHAR2- NCHAR- NVARCHAR2 - CLOB- NCLOB
There is no support for any LONG, BLOB or RAW datatypes.
- '^' The beginning of the line must start with pattern- '.' Means any valid character- '*' Means 0 or more repeating characters including whitespaces.- '$' Means end of the line must end with pattern.
additional parameters – ’i’ specifies case-insensitive matching.– ’c’ specifies case-sensitive matching.– ’n’ allows the period (.), which is the match-any-character wildcard character, to match the newline character. If omitting this parameter, the period does not match the newline character.– ’m’ treats the source string as multiple lines. Oracle interprets ^ and $ as the start and end, respectively, of any line anywhere in the source string, rather than only at the start or end of the entire source string.
描述正则表达式例子CREATE TABLE t (x VARCHAR2(30)); INSERT ALL INTO t VALUES ('XYZ123') INTO t VALUES ('XYZ 123') INTO t VALUES ('xyz 123') INTO t VALUES ('X1Y2Z3') INTO t VALUES ('123123') INTO t VALUES ('?/*.') INTO t VALUES ('/?.') SELECT * FROM dual;
SELECT * FROM t;
-----------
REGEXP_LIKE([column],[pattern],[additional parameter]);
regexp_like 当找到标准的匹配记号的时候返回true 通过第二个传入参数传入 参数有常数和元字符组成的组合字符串 方式轻巧高效
SELECT * FROM t WHERE REGEXP_LIKE(x, '[a-z] [0-9]'); -->blankSELECT * FROM t WHERE REGEXP_LIKE(x, '[a-z].[0-9]'); 任意一个字符 .SELECT * FROM t WHERE REGEXP_LIKE(x, '[a-z]?[0-9]'); 问号字符 ? 代表0次或者1次发生SELECT * FROM t WHERE REGEXP_LIKE(x, '[a-z]*[0-9]'); *通配符 代表0次或者多次先前字符SELECT * FROM t WHERE REGEXP_LIKE(x, '[A-Z]{3}'); ｛｝特定字符的发生次数SELECT * FROM t WHERE REGEXP_LIKE(x, '([A-Z][0-9]){3}'); 大写字符和三个数值SELECT * FROM t WHERE REGEXP_LIKE(x, '([A-Z][0-9]){3,}'); 大写字符和至少三个数值SELECT * FROM t WHERE REGEXP_LIKE(x, '^[0-9]+$'); ^开始 $结束 +一次或者多次发生SELECT * FROM t WHERE REGEXP_LIKE(x, '/?'); 通配符原义解释SELECT * FROM t WHERE REGEXP_LIKE(x, '[^0-9]+'); {^} 代表not 不全部都是数值SELECT * FROM t WHERE REGEXP_LIKE(x, 'X|1'); 选择通配符 |
-----------regexp_instr 返回字符串匹配的位置，可以指定开始和发生几次参数SELECT x, REGEXP_INSTR(x, '/?') AS "POSITION_OF_?" FROM t;
查找三个大写字符位置 SELECT x --<>-- , REGEXP_INSTR( x, '[A-Z]{3}', --expression 1, --start at 1, --nth occurrence 0 --offset position ) AS regexp_offset_0 --<>-- , REGEXP_INSTR( x, '[A-Z]{3}', 1, 1, 1 ) AS regexp_offset_1 --<>-- , REGEXP_INSTR( x, '[A-Z]{3}', 1, 1, 0, 'i' --match parameter ) AS regexp_case_insensitive --<>-- FROM t;
-----------regexp_substr 返回匹配格式数据
-----------regexp_replace SELECT x , REGEXP_REPLACE(x, '[[:digit:]]', '-') AS nums_to_hyphens FROM t;
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
drop table check_reg_operator;
create table check_reg_operator( contact_info varchar2(100))/
insert into check_reg_operator values('Contact number for smith is 238-564-7645');insert into check_reg_operator values('Contact number for Adam is 22-269-45');insert into check_reg_operator values('Contact number for Sumit is 64-75');insert into check_reg_operator values('Contact number for Rajeev is 4564-564-7');insert into check_reg_operator values('Contact number for Rajeev is sdas-767-9');
set linesize 200
select contact_infofrom check_reg_operatorwhere regexp_like(contact_info,'..-...')/CONTACT_INFO----------------------------------------------------------------Contact number for smith is 238-564-7645Contact number for Adam is 22-269-45Contact number for Rajeev is 4564-564-7Contact number for Rajeev is sdas-767-9
Query not to select sdas-564-7 and select only those rows having just digits on both sides of '-'
select contact_infofrom check_reg_operatorwhere regexp_like(contact_info,'[0-9]{2}-[0-9]{3}')/
CONTACT_INFO-------------------------------------------------Contact number for smith is 238-564-7645Contact number for Adam is 22-269-45Contact number for Rajeev is 4564-564-7
＝＝＝＝
CREATE TABLE EMPLOYEE1 (EMPNO NUMBER(10), FIRST_NAME VARCHAR2(10), SURNAME VARCHAR2(20));INSERT INTO EMPLOYEE1 VALUES(1,'JOHN','FREEMAN');INSERT INTO EMPLOYEE1 VALUES(2,'IAN','SCOTT');INSERT INTO EMPLOYEE1 VALUES(3,'JACK','LEE');INSERT INTO EMPLOYEE1 VALUES(4,'MIKE','BLOOM');INSERT INTO EMPLOYEE1 VALUES(5,'STEVEN','KING');INSERT INTO EMPLOYEE1 VALUES(6,'STEPHEN','JOHNSSON');COMMIT;
select * from EMPLOYEE1;
SELECT FIRST_NAME,SURNAME FROM EMPLOYEE1 WHERE REGEXP_LIKE(SURNAME,'([aeiou])/1','i')ORDER BY EMPNO ASC;
To find the text below stored in a VARCHAR2 column. 'The quick brown fox jumps over the lazy dog' or 'THE QUICK BROWN FOX JUMPS OVER THE LAZY DOG' or
any other combination of capitalized or non capitalized words in that sentence.
Using the described meta characters and additional parameters, the following example using REGEXP could be used:
SELECT ...REGEXP_LIKE (,'^The.*brown.*fox.*dog$','i');
By using the additional parameter 'i' the matching will be case-insensitive.
According to the meta characters used in the pattern the following are what weare looking for:
'The brown fox dog'
Looking at the initial example
REGEXP_LIKE(SURNAME,'([aeiou])/1','i')
This groups a pattern with ([])/1 that states that any of the characters in the pattern repeated at least one time should be a match.
With the additional parameter 'i' the search will be case-insensitive.
Another example of grouping a pattern is:
^(The|the)
This patterns state that the first letters of a row of the text have to be either T,h,e OR (|) t,h,e.
This regular expression would find STEVEN or STEPHEN in the FIRST_NAMEcolumn of the EMPLOYEE table given in the earlier example.
WHERE REGEXP_LIKE (first_name, '^Ste(v|ph)en$');
Example using regular expressions with PL/SQL---------------------------------------------
In the following example a regular expression is used for doing pattern matching using a select with table function to match values within an array ofstrings.
CREATE OR REPLACE TYPE STRING_ARRAY AS TABLE OF CLOB;/CREATE OR REPLACE FUNCTION FIND_PATTERN(MY_COLL STRING_ARRAY)RETURN STRING_ARRAY ISNEW_COLL STRING_ARRAY := STRING_ARRAY();BEGINSELECT COLUMN_VALUEBULK COLLECT INTO NEW_COLLFROM TABLE(CAST(MY_COLL AS STRING_ARRAY))WHERE REGEXP_LIKE(COLUMN_VALUE,'^Ste(v|ph)en$','i');RETURN NEW_COLL;END;/
SET SERVEROUTPUT ON
DECLAREMY_INARRAY STRING_ARRAY := STRING_ARRAY('Steven','John','STEPHEN','STEVE');MY_OUTARRAY STRING_ARRAY := STRING_ARRAY();BEGINMY_OUTARRAY := FIND_PATTERN(MY_INARRAY);FOR j IN MY_OUTARRAY.FIRST..MY_OUTARRAY.LAST LOOPDBMS_OUTPUT.PUT_LINE(MY_OUTARRAY(j));END LOOP;END;/
