Oracle PL/SQL/Regular Expressions/Wild Card
Содержание
- 1 "." matches anything
- 2 "?": match exactly zero or one repetition
- 3 "+": match one or more repetitions
- 4 "*" (match zero or more repetitions)
- 5 REGEXP_INSTR(description, "e.+e"): An "e" followed by any number of other characters and then another "e"
- 6 REGEXP_INSTR(description,"e.e"): Find a letter and then whatever until there was another of the same letter
- 7 REGEXP_INSTR(description,"F.")
- 8 REGEXP_INSTR(description,"i.i"): "i" followed by any one character, followed by another "i"
- 9 REGEXP_SUBSTR("a","ab+")
- 10 REGEXP_SUBSTR("abbbb","ab+")
- 11 REGEXP_SUBSTR("abbbb","ab*") (2)
- 12 REGEXP_SUBSTR(description,"e.*e")
- 13 REGEXP_SUBSTR("Yababa dababa do","a.a")
- 14 To see the difference between "*" and "+"
- 15 Use the non-greedy "?"
"." matches anything
<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> --"." matches anything. SQL> SQL> SELECT Description, REGEXP_INSTR(Description,"O.e") where_it_is
2 FROM testTable 3 WHERE REGEXP_INSTR(Description,"O.e") > 0;
DESCRIPTION WHERE_IT_IS
-----------
One than another 1 SQL> SQL> SQL> SQL> SQL> SQL> drop table TestTable; Table dropped. SQL> SQL> SQL>
</source>
"?": match exactly zero or one repetition
<source lang="sql">
SQL> --"?": match exactly zero or one repetition SQL> SQL> SELECT REGEXP_SUBSTR("Yababa dababa do","a.?a") FROM dual; REG --- aba SQL>
</source>
"+": match one or more repetitions
<source lang="sql">
SQL> -- "+": match one or more repetitions SQL> SQL> SELECT REGEXP_SUBSTR("Yababa dababa do","a.+a") FROM dual; REGEXP_SUBST
ababa dababa SQL>
</source>
"*" (match zero or more repetitions)
<source lang="sql">
SQL> --"*" (match zero or more repetitions): SQL> SQL> SELECT REGEXP_SUBSTR("Yababa dababa do","a.*a") FROM dual; REGEXP_SUBST
ababa dababa SQL>
</source>
REGEXP_INSTR(description, "e.+e"): An "e" followed by any number of other characters and then another "e"
<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> -- REGEXP_INSTR(description, "e.+e"): An "e" followed by any number of other characters and then another "e" SQL> SQL> SELECT description,
2 REGEXP_SUBSTR(description,"e.+e"), 3 REGEXP_INSTR(description, "e.+e") "@" 4 FROM testTable;
DESCRIPTION REGEXP_SUBSTR(DESCRIPTION,"E.+ @
------------------------------ ----------
1234 5th Street 0 1 Culloden Street en Stree 9 1234 Road 0 33 Thrid Road 0 One than another e than anothe 3 2003 Movie 0 Start With Letters ette 13 7 rows selected. SQL> SQL> drop table TestTable; Table dropped. SQL> SQL> SQL>
</source>
REGEXP_INSTR(description,"e.e"): Find a letter and then whatever until there was another of the same letter
<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","eSetarte With Letterse"); 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 eSetarte With Letterse 7 rows selected. SQL> SQL> -- Find a letter and then whatever until there was another of the same letter SQL> SQL> SQL> SELECT description,
2 REGEXP_INSTR(description,"e.e") where_pattern_starts 3 FROM testTable 4 WHERE REGEXP_INSTR(description,"e.e") > 0;
DESCRIPTION WHERE_PATTERN_STARTS
--------------------
eSetarte With Letterse 1 SQL> SQL> drop table TestTable; Table dropped. SQL> SQL>
</source>
REGEXP_INSTR(description,"F.")
<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 First 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 First Road 4 33 Thrid Road 5 One than another 6 2003 Movie 7 Start With Letters 7 rows selected. SQL> SQL> SQL> SQL> SELECT description, REGEXP_INSTR(description,"F.") where_it_is
2 FROM testTable 3 WHERE REGEXP_INSTR(description,"F.") > 0;
DESCRIPTION WHERE_IT_IS
-----------
1234 First Road 6 SQL> SQL> SQL> SQL> drop table TestTable; Table dropped. SQL> SQL> SQL>
</source>
REGEXP_INSTR(description,"i.i"): "i" followed by any one character, followed by another "i"
<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","iSitart 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 iSitart With Letters 7 rows selected. SQL> SQL> SQL> -- REGEXP_INSTR(description,"i.i"): "i" followed by any one character, followed by another "i" SQL> SQL> SELECT description,
2 REGEXP_INSTR(description,"i.i") where_pattern_starts 3 FROM testTable 4 WHERE REGEXP_INSTR(description,"i.i") > 0;
DESCRIPTION WHERE_PATTERN_STARTS
--------------------
iSitart With Letters 1 SQL> SQL> SQL> SQL> drop table TestTable; Table dropped. SQL> SQL>
</source>
REGEXP_SUBSTR("a","ab+")
<source lang="sql">
SQL> SQL> SELECT REGEXP_SUBSTR("a","ab+") FROM dual; R -
SQL>
</source>
REGEXP_SUBSTR("abbbb","ab+")
<source lang="sql">
SQL> --if we have succeeding "b"s, we get the same greedy result as with "*": SQL> SQL> SELECT REGEXP_SUBSTR("abbbb","ab+") FROM dual; REGEX
abbbb SQL> SQL>
</source>
REGEXP_SUBSTR("abbbb","ab*") (2)
<source lang="sql">
SQL> SQL> SELECT REGEXP_SUBSTR("abbbb","ab*") FROM dual; REGEX
abbbb SQL>
</source>
REGEXP_SUBSTR(description,"e.*e")
<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> -- Note the greedy "+" finding one or more things between "e"s; it "stretches" the --letters between "e"s as far as possible. Note that the query returned "eenbrie" and not just "ee". SQL> SQL> SELECT description, REGEXP_SUBSTR(description,"e.*e") FROM testTable; DESCRIPTION REGEXP_SUBSTR(DESCRIPTION,"E.*
------------------------------
1234 5th Street ee 1 Culloden Street en Stree 1234 Road 33 Thrid Road One than another e than anothe 2003 Movie Start With Letters ette 7 rows selected. SQL> SQL> SQL> SQL> drop table TestTable; Table dropped. SQL> SQL> SQL>
</source>
REGEXP_SUBSTR("Yababa dababa do","a.a")
<source lang="sql">
SQL> SELECT REGEXP_SUBSTR("Yababa dababa do","a.a") FROM dual; REG --- aba SQL>
</source>
To see the difference between "*" and "+"
<source lang="sql">
SQL> -- To see the difference between "*" and "+", consider the next four queries. SQL> SQL> -- Here, we are asking to match an "a" and zero or more "b"s: SQL> SQL> SELECT REGEXP_SUBSTR("a","ab*") FROM dual; R - a SQL> SQL>
</source>
Use the non-greedy "?"
<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> -- Use the non-greedy "?"; SQL> SQL> SELECT description,
2 REGEXP_SUBSTR(description,"e.?e") 3 FROM testTable;
DESCRIPTION REGEXP_SUBSTR(DESCRIPTION,"E.?
------------------------------
1234 5th Street ee 1 Culloden Street ee 1234 Road 33 Thrid Road One than another 2003 Movie Start With Letters 7 rows selected. SQL> SQL> drop table TestTable; Table dropped. SQL> SQL> SQL>
</source>
