Oracle PL/SQL/Regular Expressions/Brackets

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

A match for any vowel followed by an "r" or "p"

   <source lang="sql">

SQL> create table TestTable(

 2    ID                    VARCHAR2(4 BYTE)         NOT NULL,
 3    Description           VARCHAR2(40 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> SQL> -- A match for any vowel followed by an "r" or "p" SQL> SQL> SELECT description, REGEXP_INSTR(description,"[aeiou][rp]") where_it_is

 2  FROM testTable
 3  WHERE REGEXP_INSTR(description,"[aeiou][rp]") > 0;

DESCRIPTION WHERE_IT_IS


-----------

One than another 15 Start With Letters 3 SQL> SQL> drop table TestTable; Table dropped. SQL> SQL> SQL>

      </source>
   
  


REGEXP_INSTR(description,"[aeiou][aeiou]"): Look for two 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","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> SQL> -- REGEXP_INSTR(description,"[aeiou][aeiou]"): Look for two consecutive vowels: SQL> SQL> SELECT description,

 2      REGEXP_INSTR(description,"[aeiou][aeiou]")
 3      where_pattern_starts
 4  FROM testTable;

DESCRIPTION WHERE_PATTERN_STARTS


--------------------

1234 5th Street 13 1 Culloden Street 15 1234 Road 7 33 Thrid Road 11 One than another 0 2003 Movie 9 Start With Letters 0 7 rows selected. SQL> SQL> SQL> SQL> drop table TestTable; Table dropped. SQL> SQL>

      </source>
   
  


Select only addresses that contain 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","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> --Repetition Operators SQL> SQL> -- Select only addresses that contain vowels SQL> SQL> SELECT description, REGEXP_INSTR(description,"[aeiou]") where_pattern_starts

 2  FROM testTable
 3  WHERE REGEXP_INSTR(description,"[aeiou]") > 0;

DESCRIPTION WHERE_PATTERN_STARTS


--------------------

1234 5th Street 13 1 Culloden Street 4 1234 Road 7 33 Thrid Road 7 One than another 3 2003 Movie 7 Start With Letters 3 7 rows selected. SQL> SQL> SQL> SQL> drop table TestTable; Table dropped. SQL> SQL>

      </source>
   
  


[whatever]: a match of whatever set of characters is included inside the brackets in any order

   <source lang="sql">

SQL> create table TestTable(

 2    ID                    VARCHAR2(4 BYTE)         NOT NULL,
 3    Description           VARCHAR2(40 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> -- Brackets SQL> SQL> -- [whatever]: a match of whatever set of characters is included inside the brackets in any order. SQL> SQL> SELECT description, REGEXP_INSTR(description, "[ri]") where_it_is

 2  FROM testTable;

DESCRIPTION WHERE_IT_IS


-----------

1234 5th Street 12 1 Culloden Street 14 1234 Road 0 33 Thrid Road 6 One than another 16 2003 Movie 9 Start With Letters 4 7 rows selected. SQL> SQL> SQL> SQL> SQL> SQL> drop table TestTable; Table dropped. SQL> SQL>

      </source>