Oracle PL/SQL Tutorial/Regular Expressions Functions/Repitition — различия между версиями

Текущая версия на 10:08, 26 мая 2010

Содержание

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>

Oracle PL/SQL Tutorial/Regular Expressions Functions/Repitition — различия между версиями

Текущая версия на 10:08, 26 мая 2010

Содержание

A quantifier {m} matches exactly m repetitions

At least m times with: {m,}

Looking for two consecutive vowels

{m,n} where m,n specifies that the match should occur from m to n times

Repetition Operators

Three to five consecutive vowels

Writing of the RE with a repeat operator: put in curly brackets {}.

Навигация

Персональные инструменты

Пространства имён

Варианты

Просмотры

Ещё

Поиск

Разделы

Навигация

Инструменты