Oracle PL/SQL Tutorial/Regular Expressions Functions/Brackets
A match for any vowel followed by an "r" or "p"
<source lang="sql">
SQL> SQL> -- create demo table SQL> create table myTable(
2 id NUMBER(2), 3 value VARCHAR(50) 4 );
Table created. SQL> SQL> insert into myTable(id, value)values(1,"1234 4th St. Vancouver"); 1 row created. SQL> insert into myTable(id, value)values(2,"4 Maple Ct. New York"); 1 row created. SQL> insert into myTable(id, value)values(3,"4321 Green Blvd. London"); 1 row created. SQL> insert into myTable(id, value)values(4,"33 Third St. Toronto"); 1 row created. SQL> insert into myTable(id, value)values(5,"One First Drive. Queen"); 1 row created. SQL> insert into myTable(id, value)values(6,"1664 1/2 Springhill Ave"); 1 row created. SQL> insert into myTable(id, value)values(7,"665 Fall Ave. Linken"); 1 row created. SQL> SQL> select * from mytable;
ID VALUE
--------------------------------------------------
1 1234 4th St. Vancouver
2 4 Maple Ct. New York
3 4321 Green Blvd. London
4 33 Third St. Toronto
5 One First Drive. Queen
6 1664 1/2 Springhill Ave
7 665 Fall Ave. Linken
7 rows selected. SQL> SQL> SELECT value, REGEXP_INSTR(value,"[aeiou][rp]") where_it_is
2 FROM myTable 3 WHERE REGEXP_INSTR(value,"[aeiou][rp]") > 0;
VALUE WHERE_IT_IS
-----------
1234 4th St. Vancouver 21 4 Maple Ct. New York 4 33 Third St. Toronto 6 One First Drive. Queen 6 SQL> SQL> SQL> SQL> drop table myTable; Table dropped. SQL> SQL></source>
Brackets
If we use brackets, [whatever], we are asking for a match of whatever set of characters is included inside the brackets in any order.
Suppose we wanted to devise a query to find addresses where there is either an "i" or an "r."
<source lang="sql">
SQL> SQL> -- create demo table SQL> create table myTable(
2 id NUMBER(2), 3 value VARCHAR(50) 4 );
Table created. SQL> SQL> insert into myTable(id, value)values(1,"1234 4th St. Vancouver"); 1 row created. SQL> insert into myTable(id, value)values(2,"4 Maple Ct. New York"); 1 row created. SQL> insert into myTable(id, value)values(3,"4321 Green Blvd. London"); 1 row created. SQL> insert into myTable(id, value)values(4,"33 Third St. Toronto"); 1 row created. SQL> insert into myTable(id, value)values(5,"One First Drive. Queen"); 1 row created. SQL> insert into myTable(id, value)values(6,"1664 1/2 Springhill Ave"); 1 row created. SQL> insert into myTable(id, value)values(7,"665 Fall Ave. Linken"); 1 row created. SQL> SQL> select * from mytable;
ID VALUE
--------------------------------------------------
1 1234 4th St. Vancouver
2 4 Maple Ct. New York
3 4321 Green Blvd. London
4 33 Third St. Toronto
5 One First Drive. Queen
6 1664 1/2 Springhill Ave
7 665 Fall Ave. Linken
7 rows selected. SQL> SQL> SQL> SELECT value, REGEXP_INSTR(value, "[ir]") where_it_is
2 FROM myTable;
VALUE WHERE_IT_IS
-----------
1234 4th St. Vancouver 22 4 Maple Ct. New York 19 4321 Green Blvd. London 7 33 Third St. Toronto 6 One First Drive. Queen 6 1664 1/2 Springhill Ave 12 665 Fall Ave. Linken 16 7 rows selected. SQL> SQL> SQL> drop table myTable; Table dropped. SQL> SQL></source>
