Oracle PL/SQL/Regular Expressions/OR
REGEXP_SUBSTR(description,"ird|irs"): Find all occurrences of the letter sequence "irs" or "ird"
<source lang="sql">
SQL> create table TestTable(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 Description VARCHAR2(30 BYTE) 4 ) 5 /
Table created. SQL> SQL> SQL> insert into TestTable (ID, Description) values("1","1234 5th Street"); 1 row created. SQL> insert into TestTable (ID, Description) values("2","1 Culloden Street"); 1 row created. SQL> insert into TestTable (ID, Description) values("3","1234 Road"); 1 row created. SQL> insert into TestTable (ID, Description) values("4","33 Thrid Road"); 1 row created. SQL> insert into TestTable (ID, Description) values("5","One than another"); 1 row created. SQL> insert into TestTable (ID, Description) values("6","2003 Movie"); 1 row created. SQL> insert into TestTable (ID, Description) values("7","Start With Letters"); 1 row created. 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 7 rows selected. SQL> SQL> -- Grouping SQL> SQL> -- Treat a pattern as a group. SQL> SQL> -- Find all occurrences of the letter sequence "irs" or "ird" SQL> SQL> SELECT description, REGEXP_SUBSTR(description,"ird|irs")
2 FROM testTable;
DESCRIPTION REGEXP_SUBSTR(DESCRIPTION,"IRD
------------------------------
1234 5th Street 1 Culloden Street 1234 Road 33 Thrid Road One than another 2003 Movie Start With Letters 7 rows selected. SQL> SQL> drop table TestTable; Table dropped. SQL> SQL> SQL>
</source>
Specify the string using logical "OR": "|"
<source lang="sql">
SQL> create table TestTable(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 Description VARCHAR2(30 BYTE) 4 ) 5 /
Table created. SQL> SQL> SQL> insert into TestTable (ID, Description) values("1","1234 5th Street"); 1 row created. SQL> insert into TestTable (ID, Description) values("2","1 Culloden Street"); 1 row created. SQL> insert into TestTable (ID, Description) values("3","1234 Road"); 1 row created. SQL> insert into TestTable (ID, Description) values("4","33 Thrid Road"); 1 row created. SQL> insert into TestTable (ID, Description) values("5","One than another"); 1 row created. SQL> insert into TestTable (ID, Description) values("6","2003 Movie"); 1 row created. SQL> insert into TestTable (ID, Description) values("7","Start With Letters"); 1 row created. 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 7 rows selected. SQL> SQL> SQL> -- Alternation Operator SQL> SQL> -- Specify the string using logical "OR": "|" SQL> SQL> SELECT description,
2 REGEXP_INSTR(description,"r[ds]|pl") 3 FROM testTable 4 WHERE REGEXP_INSTR(description,"r[ds]|pl") > 0;
DESCRIPTION REGEXP_INSTR(DESCRIPTION,"R[DS]|PL")
------------------------------------
Start With Letters 17 SQL> SQL> SQL> SQL> SQL> drop table TestTable; Table dropped. SQL> SQL>
</source>