Oracle PL/SQL/Regular Expressions/Brackets

A match for any vowel followed by an "r" or "p"

SQL> create table TestTable(
  2    ID                    VARCHAR2(4 BYTE)         NOT NULL,
  3    Description           VARCHAR2(40 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>
SQL>
SQL> -- A match for any vowel followed by an "r" or "p"
SQL>
SQL> SELECT description, REGEXP_INSTR(description,"[aeiou][rp]") where_it_is
  2  FROM testTable
  3  WHERE REGEXP_INSTR(description,"[aeiou][rp]") > 0;
DESCRIPTION                              WHERE_IT_IS
---------------------------------------- -----------
One than another                                  15
Start With Letters                                 3
SQL>
SQL> drop table TestTable;
Table dropped.
SQL>
SQL>
SQL>

REGEXP_INSTR(description,"[aeiou][aeiou]"): Look for two consecutive vowels

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>
SQL>
SQL> -- REGEXP_INSTR(description,"[aeiou][aeiou]"): Look for two consecutive vowels:
SQL>
SQL> SELECT description,
  2      REGEXP_INSTR(description,"[aeiou][aeiou]")
  3      where_pattern_starts
  4  FROM testTable;
DESCRIPTION                    WHERE_PATTERN_STARTS
------------------------------ --------------------
1234 5th Street                                  13
1 Culloden Street                                15
1234 Road                                         7
33 Thrid Road                                    11
One than another                                  0
2003 Movie                                        9
Start With Letters                                0
7 rows selected.
SQL>
SQL>
SQL>
SQL> drop table TestTable;
Table dropped.
SQL>
SQL>

Select only addresses that contain vowels

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>
SQL> --Repetition Operators
SQL>
SQL> -- Select only addresses that contain vowels
SQL>
SQL> SELECT description, REGEXP_INSTR(description,"[aeiou]") where_pattern_starts
  2  FROM testTable
  3  WHERE REGEXP_INSTR(description,"[aeiou]") > 0;
DESCRIPTION                    WHERE_PATTERN_STARTS
------------------------------ --------------------
1234 5th Street                                  13
1 Culloden Street                                 4
1234 Road                                         7
33 Thrid Road                                     7
One than another                                  3
2003 Movie                                        7
Start With Letters                                3
7 rows selected.
SQL>
SQL>
SQL>
SQL> drop table TestTable;
Table dropped.
SQL>
SQL>

[whatever]: a match of whatever set of characters is included inside the brackets in any order

SQL> create table TestTable(
  2    ID                    VARCHAR2(4 BYTE)         NOT NULL,
  3    Description           VARCHAR2(40 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>
SQL> -- Brackets
SQL>
SQL> -- [whatever]: a match of whatever set of characters is included inside the brackets in any order.
SQL>
SQL> SELECT description, REGEXP_INSTR(description, "[ri]") where_it_is
  2  FROM testTable;
DESCRIPTION                              WHERE_IT_IS
---------------------------------------- -----------
1234 5th Street                                   12
1 Culloden Street                                 14
1234 Road                                          0
33 Thrid Road                                      6
One than another                                  16
2003 Movie                                         9
Start With Letters                                 4
7 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> drop table TestTable;
Table dropped.
SQL>
SQL>