Oracle PL/SQL/Regular Expressions/Group

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

Group the letters "ir" together by putting them in parentheses

   <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> --Group the letters "ir" together by putting them in parentheses and then parenthesizing the suffix using alternation: SQL> SQL> SELECT description, REGEXP_SUBSTR(description,"(ir)(d|s)") FROM testTable; DESCRIPTION REGEXP_SUBSTR(DESCRIPTION,"(IR


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

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

      </source>
   
  


Parenthesize both expressions

   <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> -- Parenthesize both expressions SQL> SQL> SELECT description, REGEXP_SUBSTR(description,"(ir)d|s") FROM testTable; DESCRIPTION REGEXP_SUBSTR(DESCRIPTION,"(IR


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

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

      </source>
   
  


REGEXP_SUBSTR("Yababa dababa do","(ab)")

   <source lang="sql">

SQL> --Backreference SQL> SQL> SQL> SELECT REGEXP_SUBSTR("Yababa dababa do","(ab)") FROM dual; RE -- ab SQL>


      </source>
   
  


REGEXP_SUBSTR("Yababa dababa do","(ab)\1"), backreference option

   <source lang="sql">

SQL> --pattern is matched. If we use the backreference option, the query looks like this: SQL> SQL> SELECT REGEXP_SUBSTR("Yababa dababa do","(ab)\1")FROM dual; REGE


abab SQL>

      </source>
   
  


Using backreference: REGEXP_REPLACE("H1234 H4321 H2345","(.*) (.*) (.*)","\3, \2 \1")

   <source lang="sql">

SQL> --For a better example of using backreference, let"s suppose we wanted to convert a name in the form "first middle last" into the "last, middle first" format. Consider this command: SQL> SQL> SELECT REGEXP_REPLACE("H1234 H4321 H2345","(.*) (.*) (.*)","\3, \2 \1")FROM dual; REGEXP_REPLACE("H1


H2345, H4321 H1234 SQL> SQL>

      </source>