Oracle PL/SQL/Regular Expressions/Repeat Operator
Содержание
A repeat operator: {}. Repeating the vowel match a second time
<source lang="sql">
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> -- A repeat operator: {}. Repeating the vowel match a second time. SQL> SQL> SELECT description,
2 REGEXP_INSTR(description,"[aeiou]{2}") where_pattern_starts
3 FROM testTable
4 WHERE REGEXP_INSTR(description,"[aeiou]{2}") > 0;
DESCRIPTION WHERE_PATTERN_STARTS
--------------------
1234 5th Street 13 1 Culloden Street 15 1234 Road 7 33 Thrid Road 11 2003 Movie 9 SQL> SQL> drop table TestTable; Table dropped. SQL> SQL> SQL>
</source>
REGEXP_INSTR(description,"[aeiou]{2,3}"): Matches from two to three times
<source lang="sql">
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> -- REGEXP_INSTR(description,"[aeiou]{2,3}"): Matches from two to three times SQL> SQL> SELECT description,
2 REGEXP_INSTR(description,"[aeiou]{2,3}") where_pattern_starts
3 FROM testTable
4 WHERE REGEXP_INSTR(description,"[aeiou]{2,3}") > 0;
DESCRIPTION WHERE_PATTERN_STARTS
--------------------
1234 5th Street 13 1 Culloden Street 15 1234 Road 7 33 Thrid Road 11 2003 Movie 9 SQL> SQL> SQL> SQL> drop table TestTable; Table dropped. SQL> SQL>
</source>
REGEXP_INSTR(description,"[aeiou]{3,5}"): Specify from three to five consecutive vowels
<source lang="sql">
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","aeiou 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 aeiou With Letters 7 rows selected. SQL> SQL> SQL> -- REGEXP_INSTR(description,"[aeiou]{3,5}"): Specify from three to five consecutive vowels, we"d get this: SQL> SQL> SELECT description,
2 REGEXP_INSTR(description,"[aeiou]{2,3}") where_pattern_starts
3 FROM testTable
4 WHERE REGEXP_INSTR(description,"[aeiou]{3,5}") > 0;
DESCRIPTION WHERE_PATTERN_STARTS
--------------------
aeiou With Letters 1 SQL> SQL> SQL> SQL> SQL> drop table TestTable; Table dropped. SQL> SQL>
</source>
Repetition operator would say, "at least m times" with {m,}
<source lang="sql">
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","aeiou 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 aeiou With Letters 7 rows selected. SQL> SQL> SQL> SQL> -- Repetition operator would say, "at least m times" with {m,}: SQL> SQL> SELECT description,
2 REGEXP_INSTR(description,"[aeiou]{2,3}")
3 where_pattern_starts
4 FROM testTable
5 WHERE REGEXP_INSTR(description,"[aeiou]{3,}") > 0;
DESCRIPTION WHERE_PATTERN_STARTS
--------------------
aeiou With Letters 1 SQL> SQL> SQL> drop table TestTable; Table dropped. SQL> SQL> SQL>
</source>
