Oracle PL/SQL Tutorial/Regular Expressions Functions/Repitition
Содержание
- 1 A quantifier {m} matches exactly m repetitions
- 2 At least m times with: {m,}
- 3 Looking for two consecutive vowels
- 4 {m,n} where m,n specifies that the match should occur from m to n times
- 5 Repetition Operators
- 6 Three to five consecutive vowels
- 7 Writing of the RE with a repeat operator: put in curly brackets {}.
A quantifier {m} matches exactly m repetitions
<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,
2 REGEXP_INSTR(value,"[aeiou]{2}") where_pattern_starts
3 FROM myTable
4 WHERE REGEXP_INSTR(value,"[aeiou]{2}") > 0;
VALUE WHERE_PATTERN_STARTS
--------------------
1234 4th St. Vancouver 18 4321 Green Blvd. London 8 One First Drive. Queen 19 SQL> SQL> SQL> drop table myTable; Table dropped.</source>
At least m times with: {m,}
<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,
2 REGEXP_INSTR(value,"[aeiou]{2,3}")
3 where_pattern_starts
4 FROM myTable
5 WHERE REGEXP_INSTR(value,"[aeiou]{3,}") > 0;
VALUE WHERE_PATTERN_STARTS
--------------------
One First Drive. Queen 19 SQL> SQL> SQL> drop table myTable; Table dropped. SQL> SQL></source>
Looking for two consecutive vowels
<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,
2 REGEXP_INSTR(value,"[aeiou][aeiou]") 3 where_pattern_starts 4 FROM myTable;
VALUE WHERE_PATTERN_STARTS
--------------------
1234 4th St. Vancouver 18 4 Maple Ct. New York 0 4321 Green Blvd. London 8 33 Third St. Toronto 0 One First Drive. Queen 19 1664 1/2 Springhill Ave 0 665 Fall Ave. Linken 0 7 rows selected. SQL> SQL> SQL> SQL> drop table myTable; Table dropped. SQL> SQL></source>
{m,n} where m,n specifies that the match should occur from m to n times
Match from two to three times:
<source lang="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,
2 REGEXP_INSTR(value,"[aeiou]{2,3}") where_pattern_starts
3 FROM myTable
4 WHERE REGEXP_INSTR(value,"[aeiou]{2,3}") > 0;
VALUE WHERE_PATTERN_STARTS
--------------------
1234 4th St. Vancouver 18 4321 Green Blvd. London 8 One First Drive. Queen 19 SQL> SQL> SQL> drop table myTable; Table dropped. SQL> SQL></source>
Repetition Operators
Repeat a particular pattern.
Select only addresses that contain vowels we can use this statement:
<source lang="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]")
2 where_pattern_starts 3 FROM myTable 4 WHERE REGEXP_INSTR(value,"[aeiou]") > 0;
VALUE WHERE_PATTERN_STARTS
--------------------
1234 4th St. Vancouver 15 4 Maple Ct. New York 4 4321 Green Blvd. London 8 33 Third St. Toronto 6 One First Drive. Queen 3 1664 1/2 Springhill Ave 13 665 Fall Ave. Linken 6 7 rows selected. SQL> SQL> SQL> SQL> drop table myTable; Table dropped. SQL> SQL></source>
Three to five consecutive vowels
<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> SQL> SELECT addr,
2 REGEXP_INSTR(addr,"[aeiou]{3,5}") where_pattern_starts
3 FROM addresses
4 WHERE REGEXP_INSTR(addr,"[aeiou]{3,5}") > 0;
FROM addresses
*
ERROR at line 3: ORA-00942: table or view does not exist
SQL> SQL> SQL> drop table myTable; Table dropped. SQL> SQL></source>
Writing of the RE with a repeat operator: put in curly brackets {}.
Here is an example of repeating the vowel match a second time:
<source lang="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,
2 REGEXP_INSTR(value,"[aeiou]{2}") where_pattern_starts
3 FROM myTable
4 WHERE REGEXP_INSTR(value,"[aeiou]{2}") > 0;
VALUE WHERE_PATTERN_STARTS
--------------------
1234 4th St. Vancouver 18 4321 Green Blvd. London 8 One First Drive. Queen 19 SQL> SQL> SQL> drop table myTable; Table dropped. SQL> SQL></source>
