星期四, 11月 15, 2012

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

沒有留言:

LinkWithin-相關文件

Related Posts Plugin for WordPress, Blogger...