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
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.
At least m times with: {m,}
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>
Looking for two consecutive vowels
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>
{m,n} where m,n specifies that the match should occur from m to n times
Match from two to three times:
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>
Repetition Operators
Repeat a particular pattern.
Select only addresses that contain vowels we can use this statement:
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>
Three to five consecutive vowels
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>
Writing of the RE with a repeat operator: put in curly brackets {}.
Here is an example of repeating the vowel match a second time:
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>