Oracle PL/SQL/Regular Expressions/Group
Содержание
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>