正则表达式

4 new functions has been introduced:-
REGEXP_LIKE
REGEXP_REPLACE
REGEXP_INSTR
REGEXP_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]');  -->blank
SELECT * 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_info
from check_reg_operator
where regexp_like(contact_info,'..-...')
/
CONTACT_INFO
----------------------------------------------------------------
Contact number for smith is 238-564-7645
Contact number for Adam  is 22-269-45
Contact number for Rajeev is 4564-564-7
Contact 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_info
from check_reg_operator
where regexp_like(contact_info,'[0-9]{2}-[0-9]{3}')
/

CONTACT_INFO
-------------------------------------------------
Contact number for smith is 238-564-7645
Contact number for Adam  is 22-269-45
Contact 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 we
are 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_NAME
column 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 of
strings.

CREATE OR REPLACE TYPE STRING_ARRAY AS TABLE OF CLOB;
/
CREATE OR REPLACE FUNCTION FIND_PATTERN(MY_COLL STRING_ARRAY)
RETURN STRING_ARRAY IS
NEW_COLL STRING_ARRAY := STRING_ARRAY();
BEGIN
SELECT COLUMN_VALUE
BULK COLLECT INTO NEW_COLL
FROM TABLE(CAST(MY_COLL AS STRING_ARRAY))
WHERE REGEXP_LIKE(COLUMN_VALUE,'^Ste(v|ph)en$','i');
RETURN NEW_COLL;
END;
/

SET SERVEROUTPUT ON

DECLARE
MY_INARRAY STRING_ARRAY := STRING_ARRAY('Steven','John','STEPHEN','STEVE');
MY_OUTARRAY STRING_ARRAY := STRING_ARRAY();
BEGIN
MY_OUTARRAY := FIND_PATTERN(MY_INARRAY);
FOR j IN MY_OUTARRAY.FIRST..MY_OUTARRAY.LAST LOOP
DBMS_OUTPUT.PUT_LINE(MY_OUTARRAY(j));
END LOOP;
END;
/