Oracle PL/SQL/Regular Expressions/REGEXP SUBSTR

REGEXP_SUBSTR(description,"ird|irs"): Treat a pattern as a group

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>

REGEXP_SUBSTR("Mississippi", "si", 1, 2, "i")

SQL> SELECT REGEXP_SUBSTR("Mississippi", "si", 1, 2, "i") FROM dual;
RE
--
si
SQL>

REGEXP_SUBSTR("Yababa dababa do","(ab)\1")

SQL> --pattern is matched. If we use the backreference option, the query looks like this:
SQL>
SQL> SELECT REGEXP_SUBSTR("Yababa dababa do","(ab)\1")FROM dual;
REGE
----
abab
SQL>