Oracle PL/SQL Tutorial/Regular Expressions Functions/Brackets
A match for any vowel followed by an "r" or "p"
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>
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."
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>