в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T10:02:10Z

1 версия

Новая страница

=="." 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>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL/Regular Expressions/Wild Card - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия