Oracle PL/SQL Tutorial/Regular Expressions Functions/Repitition

Материал из SQL эксперт
Версия от 10:08, 26 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

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