[SQL] Oracle regular expression test

http://www.oracle.com/technology/obe/obe10gdb/develop/regexp/regexp.htm

https://forums.oracle.com/thread/2148778
------------------------------------------------------------------------------

create table TestTable(

 ID                    VARCHAR2(4 BYTE)         NOT NULL,

 Description           VARCHAR2(30 BYTE)

 );

insert into TestTable (ID, Description) values('1','1234 5th Street');

insert into TestTable (ID, Description) values('2','1 Culloden Street');

insert into TestTable (ID, Description) values('3','1234 Road');

insert into TestTable (ID, Description) values('4','33 Thrid Road');

insert into TestTable (ID, Description) values('5','One than another');

insert into TestTable (ID, Description) values('6','2003 Movie');

insert into TestTable (ID, Description) values('7','Start With Letters');

commit;

/

insert into testTable values ('1' , 'mn');

insert into testTable values ('1' , 'nn');

insert into testTable values ('1' , 'nmmmn');

insert into testTable values ('1' , 'Alaxendar');

insert into testTable values ('1' , 'Alexender');

SQL>

SQL> select * from TestTable;

ID   DESCRIPTION

---- ------------------------------

1    1234 5th Street

2    1 Culloden Street

3    1234 Road

4    33 Thrid Road

5    One than another

6    2003 Movie

7    Start With Letters

[] - matching any one of the expressions represented in the list

SELECT * FROM testTable WHERE REGEXP_LIKE(description,'[*]'); --no row selected

SELECT * FROM testTable WHERE REGEXP_LIKE(description,'[A*]');

SELECT * FROM testTable WHERE REGEXP_LIKE(description,'[^Alaxendar]');

SELECT * FROM testTable WHERE REGEXP_LIKE(description,'[^Alexender]');

SELECT * FROM testTable WHERE REGEXP_LIKE(description,'[^Alaxend.r]');

SELECT * FROM testTable WHERE REGEXP_LIKE(description,'[$]');

SELECT * FROM testTable WHERE REGEXP_LIKE(description,'[mmmm]');--任何包含m的都秀出來

SELECT * FROM testTable WHERE REGEXP_LIKE(description,'[^m(n)]'); --任何包含m or n 的都不秀出來

SELECT * FROM testTable WHERE REGEXP_LIKE(description,'[^$]');  --如果是null紀錄才不會秀出來

SELECT * FROM testTable WHERE REGEXP_LIKE(description,'[ALE|ax.r]');

---
另外regular expression 也可以被建在functional index, 如下為例:

Scott@my11g SQL>create table test (name varchar2(30));

Table created.

Scott@my11g SQL>create index myfbi on test(case when regexp_like(name,'^98721[0-9]*[5]+[0-9]*$') then 1 else 0 end);

Index created.

Scott@my11g SQL>explain plan for
  2  select * from test where case when regexp_like(name,'^98721[0-9]*[5]+[0-9]*$') then 1 else 0 end = 1;

Explained.

Scott@my11g SQL>/

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 140237472

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    20 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST  |     1 |    20 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | MYFBI |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(CASE  WHEN  REGEXP_LIKE ("NAME",'^98721[0-9]*[5]+[0-9]*$')
              THEN 1 ELSE 0 END =1)

Note
-----
   - dynamic sampling used for this statement (level=2)

19 rows selected.