Oracle PL/SQL/Regular Expressions/Repeat Operator

Материал из SQL эксперт
Перейти к: навигация, поиск

A repeat operator: {}. Repeating the vowel match a second time

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>



REGEXP_INSTR(description,"[aeiou]{2,3}"): Matches from two to three times

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>



REGEXP_INSTR(description,"[aeiou]{3,5}"): Specify from three to five consecutive vowels

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>



Repetition operator would say, "at least m times" with {m,}

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>
Источник — «http://sqle.ru/index.php?title=Oracle_PL/SQL/Regular_Expressions/Repeat_Operator&oldid=2643»