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
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>
"?": match exactly zero or one repetition
SQL> --"?": match exactly zero or one repetition
SQL>
SQL> SELECT REGEXP_SUBSTR("Yababa dababa do","a.?a") FROM dual;
REG
---
aba
SQL>
"+": match one or more repetitions
SQL> -- "+": match one or more repetitions
SQL>
SQL> SELECT REGEXP_SUBSTR("Yababa dababa do","a.+a") FROM dual;
REGEXP_SUBST
------------
ababa dababa
SQL>
"*" (match zero or more repetitions)
SQL> --"*" (match zero or more repetitions):
SQL>
SQL> SELECT REGEXP_SUBSTR("Yababa dababa do","a.*a") FROM dual;
REGEXP_SUBST
------------
ababa dababa
SQL>
REGEXP_INSTR(description, "e.+e"): An "e" followed by any number of other characters and then another "e"
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>
REGEXP_INSTR(description,"e.e"): Find a letter and then whatever until there was another of the same letter
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>
REGEXP_INSTR(description,"F.")
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>
REGEXP_INSTR(description,"i.i"): "i" followed by any one character, followed by another "i"
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>
REGEXP_SUBSTR("a","ab+")
SQL>
SQL> SELECT REGEXP_SUBSTR("a","ab+") FROM dual;
R
-
SQL>
REGEXP_SUBSTR("abbbb","ab+")
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>
REGEXP_SUBSTR("abbbb","ab*") (2)
SQL>
SQL> SELECT REGEXP_SUBSTR("abbbb","ab*") FROM dual;
REGEX
-----
abbbb
SQL>
REGEXP_SUBSTR(description,"e.*e")
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>
REGEXP_SUBSTR("Yababa dababa do","a.a")
SQL> SELECT REGEXP_SUBSTR("Yababa dababa do","a.a") FROM dual;
REG
---
aba
SQL>
To see the difference between "*" and "+"
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>
Use the non-greedy "?"
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>