в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:08:40Z

1 версия

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

== A "^" followed by something else like an "l" (a lowercase "L")==



<source lang="sql">
SQL>
SQL> -- create demo table
SQL> create table myTable(
2 id NUMBER(2),
3 value VARCHAR(50)
4 );
Table created.
SQL>
SQL> insert into myTable(id, value)values(1,"1234 4th St. Vancouver");
1 row created.
SQL> insert into myTable(id, value)values(2,"4 Maple Ct. New York");
1 row created.
SQL> insert into myTable(id, value)values(3,"4321 Green Blvd. London");
1 row created.
SQL> insert into myTable(id, value)values(4,"33 Third St. Toronto");
1 row created.
SQL> insert into myTable(id, value)values(5,"One First Drive. Queen");
1 row created.
SQL> insert into myTable(id, value)values(6,"1664 1/2 Springhill Ave");
1 row created.
SQL> insert into myTable(id, value)values(7,"665 Fall Ave. Linken");
1 row created.
SQL>
SQL> select * from mytable;
ID VALUE
---------- --------------------------------------------------
1 1234 4th St. Vancouver
2 4 Maple Ct. New York
3 4321 Green Blvd. London
4 33 Third St. Toronto
5 One First Drive. Queen
6 1664 1/2 Springhill Ave
7 665 Fall Ave. Linken
7 rows selected.
SQL>
SQL>
SQL> SELECT value
2 FROM myTable
3 WHERE REGEXP_LIKE(value,"[^gp]l");
VALUE
--------------------------------------------------
4321 Green Blvd. London
1664 1/2 Springhill Ave
665 Fall Ave. Linken
SQL>
SQL>
SQL> drop table myTable;
Table dropped.</source>

== A series of "b"s immediately following the "a"==



<source lang="sql">
SQL>
SQL> SELECT REGEXP_SUBSTR("abbbb","ab*") FROM dual;
REGEX
-----
abbbb</source>

== Asking for a match for a capital "F" followed by any character==



<source lang="sql">
SQL>
SQL> -- create demo table
SQL> create table myTable(
2 id NUMBER(2),
3 value VARCHAR(50)
4 );
Table created.
SQL>
SQL> insert into myTable(id, value)values(1,"1234 4th St. Vancouver");
1 row created.
SQL> insert into myTable(id, value)values(2,"4 Maple Ct. New York");
1 row created.
SQL> insert into myTable(id, value)values(3,"4321 Green Blvd. London");
1 row created.
SQL> insert into myTable(id, value)values(4,"33 Third St. Toronto");
1 row created.
SQL> insert into myTable(id, value)values(5,"One First Drive. Queen");
1 row created.
SQL> insert into myTable(id, value)values(6,"1664 1/2 Springhill Ave");
1 row created.
SQL> insert into myTable(id, value)values(7,"665 Fall Ave. Linken");
1 row created.
SQL>
SQL> select * from mytable;
ID VALUE
---------- --------------------------------------------------
1 1234 4th St. Vancouver
2 4 Maple Ct. New York
3 4321 Green Blvd. London
4 33 Third St. Toronto
5 One First Drive. Queen
6 1664 1/2 Springhill Ave
7 665 Fall Ave. Linken
7 rows selected.
SQL>
SQL>
SQL> SELECT value, REGEXP_INSTR(value,"^F.") where_it_is
2 FROM myTable
3 WHERE REGEXP_INSTR(value,"^F.") > 0;
no rows selected
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL></source>

== Asking to match an "a" and zero or more "b"s:==



<source lang="sql">
SQL>
SQL> SELECT REGEXP_SUBSTR("a","ab*") FROM dual;
R
-
a
SQL></source>

== Empty Strings and the ? Repetition Character==

The "?" metacharacter seeks to match zero or one repetition of a pattern. This characteristic works well as long as one expects some match to occur.



<source lang="sql">
SQL>
SQL> SELECT REGEXP_INSTR("abc","d") FROM dual;
REGEXP_INSTR("ABC","D")
-----------------------
0
SQL></source>

== Match a string where "i" is followed by any one character and followed by another "i"==



<source lang="sql">
SQL>
SQL> -- create demo table
SQL> create table myTable(
2 id NUMBER(2),
3 value VARCHAR(50)
4 );
Table created.
SQL>
SQL> insert into myTable(id, value)values(1,"1234 4th St. Vancouver");
1 row created.
SQL> insert into myTable(id, value)values(2,"4 Maple Ct. New York");
1 row created.
SQL> insert into myTable(id, value)values(3,"4321 Green Blvd. London");
1 row created.
SQL> insert into myTable(id, value)values(4,"33 Third St. Toronto");
1 row created.
SQL> insert into myTable(id, value)values(5,"One First Drive. Queen");
1 row created.
SQL> insert into myTable(id, value)values(6,"1664 1/2 Springhill Ave");
1 row created.
SQL> insert into myTable(id, value)values(7,"665 Fall Ave. Linken");
1 row created.
SQL>
SQL> select * from mytable;
ID VALUE
---------- --------------------------------------------------
1 1234 4th St. Vancouver
2 4 Maple Ct. New York
3 4321 Green Blvd. London
4 33 Third St. Toronto
5 One First Drive. Queen
6 1664 1/2 Springhill Ave
7 665 Fall Ave. Linken
7 rows selected.
SQL>
SQL>
SQL> SELECT value,
2 REGEXP_INSTR(value,"i.i") where_pattern_starts
3 FROM myTable
4 WHERE REGEXP_INSTR(value,"i.i") > 0;
no rows selected
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL></source>

== "?" matches exactly zero or one repetition==



<source lang="sql">
SQL>
SQL>
SQL> SELECT REGEXP_SUBSTR("Yababa dababa do","a.?a") FROM dual;
REG
---
aba
SQL></source>

== "+" matches one or more repetitions==



<source lang="sql">
SQL>
SQL> SELECT REGEXP_SUBSTR("Yababa dababa do","a.+a") FROM dual;
REGEXP_SUBST
------------
ababa dababa
SQL></source>

== "*" matches zero or more repetitions==



<source lang="sql">
SQL>
SQL>
SQL> SELECT REGEXP_SUBSTR("Yababa dababa do","a.*a") FROM dual;
REGEXP_SUBST
------------
ababa dababa
SQL></source>

== Matching at least one "b"==



<source lang="sql">
SQL>
SQL> SELECT REGEXP_SUBSTR("a","ab+") FROM dual;
R
-

SQL></source>

== Meta characters==

In Regrlar Expresions, there are three special characters that are used in matching patterns:
<OL><LI>"^" - a caret is called an "anchoring operator," and matches the beginning of a string. The caret may also mean "not," which is at best confusing.</LI><LI>"$" - a dollar sign is another anchoring operator and matches only the end of a string.</LI><LI>"." - the period matches anything and is called the "match any character" operator. Many would call this a "wildcard" match character.</LI><LI>+ matches one or more repetitions of the preceding RE</LI><LI>* matches zero or more repetitions of the preceding RE</LI><LI>? matches zero or one repetition of the preceding RE</LI></OL>
18. 6. Meta characters
18. 6. 1.
Meta characters
18. 6. 2.
<A href="/Tutorial/Oracle/0360__Regular-Expressions-Functions/Theperiodmaybesubstitutedforanyletterandstillmaintainamatch.htm">The period may be substituted for any letter and still maintain a match</a>
18. 6. 3.
<A href="/Tutorial/Oracle/0360__Regular-Expressions-Functions/Usethecaretanchortoinsistthematchingstartatthebeginningofthestring.htm">Use the caret-anchor to insist the matching start at the beginning of the string</a>
18. 6. 4.
<A href="/Tutorial/Oracle/0360__Regular-Expressions-Functions/AskingforamatchforacapitalFfollowedbyanycharacter.htm">Asking for a match for a capital "F" followed by any character</a>
18. 6. 5.
<A href="/Tutorial/Oracle/0360__Regular-Expressions-Functions/Thesearchstringcannotbeanchoredatthebeginningandthensearchedfromsomeotherposition.htm">The search string cannot be anchored at the beginning and then searched from some other position</a>
18. 6. 6.
<A href="/Tutorial/Oracle/0360__Regular-Expressions-Functions/NegatingCarets.htm">Negating Carets</a>
18. 6. 7.
<A href="/Tutorial/Oracle/0360__Regular-Expressions-Functions/AfollowedbysomethingelselikeanlalowercaseL.htm">A "^" followed by something else like an "l" (a lowercase "L")</a>
18. 6. 8.
<A href="/Tutorial/Oracle/0360__Regular-Expressions-Functions/Matchastringwhereiisfollowedbyanyonecharacterandfollowedbyanotheri.htm">Match a string where "i" is followed by any one character and followed by another "i"</a>
18. 6. 9.
<A href="/Tutorial/Oracle/0360__Regular-Expressions-Functions/RegexpSubstr.htm">Regexp_Substr</a>
18. 6. 10.
<A href="/Tutorial/Oracle/0360__Regular-Expressions-Functions/matcheszeroormorerepetitions.htm">"*" matches zero or more repetitions</a>
18. 6. 11.
<A href="/Tutorial/Oracle/0360__Regular-Expressions-Functions/matchesoneormorerepetitions.htm">"+" matches one or more repetitions</a>
18. 6. 12.
<A href="/Tutorial/Oracle/0360__Regular-Expressions-Functions/matchesexactlyzerooronerepetition.htm">"?" matches exactly zero or one repetition</a>
18. 6. 13.
<A href="/Tutorial/Oracle/0360__Regular-Expressions-Functions/Askingtomatchanaandzeroormorebs.htm">Asking to match an "a" and zero or more "b"s:</a>
18. 6. 14.
<A href="/Tutorial/Oracle/0360__Regular-Expressions-Functions/Aseriesofbsimmediatelyfollowingthea.htm">A series of "b"s immediately following the "a"</a>
18. 6. 15.
<A href="/Tutorial/Oracle/0360__Regular-Expressions-Functions/Matchingatleastoneb.htm">Matching at least one "b"</a>
18. 6. 16.
<A href="/Tutorial/Oracle/0360__Regular-Expressions-Functions/Wantanefollowedbyanynumberofothercharactersandthenanothere.htm">Want an "e" followed by any number of other characters and then another "e"</a>
18. 6. 17.
<A href="/Tutorial/Oracle/0360__Regular-Expressions-Functions/REGEXPSUBSTRvalueee.htm">REGEXP_SUBSTR(value,"e.*e")</a>
18. 6. 18.
<A href="/Tutorial/Oracle/0360__Regular-Expressions-Functions/Nongreedy.htm">Non-greedy "?"</a>
18. 6. 19.
<A href="/Tutorial/Oracle/0360__Regular-Expressions-Functions/EmptyStringsandtheRepetitionCharacter.htm">Empty Strings and the ? Repetition Character</a>
18. 6. 20.
<A href="/Tutorial/Oracle/0360__Regular-Expressions-Functions/Thesaystomatchzerooronetime.htm">The "?" says to match zero or one time</a>

== Negating Carets==

The caret ("^") may be either an anchor or a negating marker.
We may negate the string we are looking for by placing a negating caret at the beginning of the string like this:



<source lang="sql">
SQL> -- create demo table
SQL> create table myTable(
2 id NUMBER(2),
3 value VARCHAR(50)
4 );
Table created.
SQL>
SQL> insert into myTable(id, value)values(1,"1234 4th St. Vancouver");
1 row created.
SQL> insert into myTable(id, value)values(2,"4 Maple Ct. New York");
1 row created.
SQL> insert into myTable(id, value)values(3,"4321 Green Blvd. London");
1 row created.
SQL> insert into myTable(id, value)values(4,"33 Third St. Toronto");
1 row created.
SQL> insert into myTable(id, value)values(5,"One First Drive. Queen");
1 row created.
SQL> insert into myTable(id, value)values(6,"1664 1/2 Springhill Ave");
1 row created.
SQL> insert into myTable(id, value)values(7,"665 Fall Ave. Linken");
1 row created.
SQL>
SQL> select * from mytable;
ID VALUE
---------- --------------------------------------------------
1 1234 4th St. Vancouver
2 4 Maple Ct. New York
3 4321 Green Blvd. London
4 33 Third St. Toronto
5 One First Drive. Queen
6 1664 1/2 Springhill Ave
7 665 Fall Ave. Linken
7 rows selected.
SQL>
SQL>
SQL> SELECT value
2 FROM myTable
3 WHERE REGEXP_LIKE(value,"[^gp]");
VALUE
--------------------------------------------------
1234 4th St. Vancouver
4 Maple Ct. New York
4321 Green Blvd. London
33 Third St. Toronto
One First Drive. Queen
1664 1/2 Springhill Ave
665 Fall Ave. Linken
7 rows selected.
SQL>
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL></source>

== Non-greedy "?"==



<source lang="sql">
SQL>
SQL> -- create demo table
SQL> create table myTable(
2 id NUMBER(2),
3 value VARCHAR(50)
4 );
Table created.
SQL>
SQL> insert into myTable(id, value)values(1,"1234 4th St. Vancouver");
1 row created.
SQL> insert into myTable(id, value)values(2,"4 Maple Ct. New York");
1 row created.
SQL> insert into myTable(id, value)values(3,"4321 Green Blvd. London");
1 row created.
SQL> insert into myTable(id, value)values(4,"33 Third St. Toronto");
1 row created.
SQL> insert into myTable(id, value)values(5,"One First Drive. Queen");
1 row created.
SQL> insert into myTable(id, value)values(6,"1664 1/2 Springhill Ave");
1 row created.
SQL> insert into myTable(id, value)values(7,"665 Fall Ave. Linken");
1 row created.
SQL>
SQL> select * from mytable;
ID VALUE
---------- --------------------------------------------------
1 1234 4th St. Vancouver
2 4 Maple Ct. New York
3 4321 Green Blvd. London
4 33 Third St. Toronto
5 One First Drive. Queen
6 1664 1/2 Springhill Ave
7 665 Fall Ave. Linken
7 rows selected.
SQL>
SQL>
SQL> SELECT value,
2 REGEXP_SUBSTR(value,"e.?e")
3 FROM myTable;
VALUE REGEXP_SUBSTR(VALUE,"E.?E")
-------------------------------------------------- -------------------------------------------------
-
1234 4th St. Vancouver
4 Maple Ct. New York
4321 Green Blvd. London ee
33 Third St. Toronto
One First Drive. Queen ee
1664 1/2 Springhill Ave
665 Fall Ave. Linken
7 rows selected.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL></source>

== Regexp_Substr==

REGEXP_SUBSTR returns part of a string.
The complete syntax of REGEXP_SUBSTR is:



<source lang="sql">
REGEXP_SUBSTR(String to search, Pattern, [Position, [Occurrence, [Return-option, [Parameters]]]])</source>

== REGEXP_SUBSTR(value,"e.*e")==



<source lang="sql">
SQL> -- create demo table
SQL> create table myTable(
2 id NUMBER(2),
3 value VARCHAR(50)
4 );
Table created.
SQL>
SQL> insert into myTable(id, value)values(1,"1234 4th St. Vancouver");
1 row created.
SQL> insert into myTable(id, value)values(2,"4 Maple Ct. New York");
1 row created.
SQL> insert into myTable(id, value)values(3,"4321 Green Blvd. London");
1 row created.
SQL> insert into myTable(id, value)values(4,"33 Third St. Toronto");
1 row created.
SQL> insert into myTable(id, value)values(5,"One First Drive. Queen");
1 row created.
SQL> insert into myTable(id, value)values(6,"1664 1/2 Springhill Ave");
1 row created.
SQL> insert into myTable(id, value)values(7,"665 Fall Ave. Linken");
1 row created.
SQL>
SQL> select * from mytable;
ID VALUE
---------- --------------------------------------------------
1 1234 4th St. Vancouver
2 4 Maple Ct. New York
3 4321 Green Blvd. London
4 33 Third St. Toronto
5 One First Drive. Queen
6 1664 1/2 Springhill Ave
7 665 Fall Ave. Linken
7 rows selected.
SQL>
SQL> SELECT value,
2 REGEXP_SUBSTR(value,"e.*e")
3 FROM myTable;
VALUE REGEXP_SUBSTR(VALUE,"E.*E")
-------------------------------------------------- -------------------------------------------------
-
1234 4th St. Vancouver
4 Maple Ct. New York e Ct. Ne
4321 Green Blvd. London ee
33 Third St. Toronto
One First Drive. Queen e First Drive. Quee
1664 1/2 Springhill Ave
665 Fall Ave. Linken e. Linke
7 rows selected.
SQL>
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL>
SQL></source>

== The period may be substituted for any letter and still maintain a match==



<source lang="sql">
SQL>
SQL> -- create demo table
SQL> create table myTable(
2 id NUMBER(2),
3 value VARCHAR(50)
4 );
Table created.
SQL>
SQL> insert into myTable(id, value)values(1,"1234 4th St. Vancouver");
1 row created.
SQL> insert into myTable(id, value)values(2,"4 Maple Ct. New York");
1 row created.
SQL> insert into myTable(id, value)values(3,"4321 Green Blvd. London");
1 row created.
SQL> insert into myTable(id, value)values(4,"33 Third St. Toronto");
1 row created.
SQL> insert into myTable(id, value)values(5,"One First Drive. Queen");
1 row created.
SQL> insert into myTable(id, value)values(6,"1664 1/2 Springhill Ave");
1 row created.
SQL> insert into myTable(id, value)values(7,"665 Fall Ave. Linken");
1 row created.
SQL>
SQL> select * from mytable;
ID VALUE
---------- --------------------------------------------------
1 1234 4th St. Vancouver
2 4 Maple Ct. New York
3 4321 Green Blvd. London
4 33 Third St. Toronto
5 One First Drive. Queen
6 1664 1/2 Springhill Ave
7 665 Fall Ave. Linken
7 rows selected.
SQL>
SQL>
SQL> SELECT value, REGEXP_INSTR(value,"O.e") where_it_is
2 FROM myTable
3 WHERE REGEXP_INSTR(value,"O.e") > 0;
VALUE WHERE_IT_IS
-------------------------------------------------- -----------
One First Drive. Queen 1
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL></source>

== The "?" says to match zero or one time==



<source lang="sql">
SQL>
SQL> SELECT REGEXP_INSTR("abc","d?") FROM dual;
REGEXP_INSTR("ABC","D?")
------------------------
1
SQL>
SQL> --.
SQL></source>

== The search string cannot be anchored at the beginning and then searched from some other position==



<source lang="sql">
SQL>
SQL> SELECT REGEXP_INSTR("Hello","^.",2)
2 FROM dual;
REGEXP_INSTR("HELLO","^.",2)
----------------------------
0
SQL></source>

== Use the caret-anchor to insist the matching start at the beginning of the string==



<source lang="sql">
SQL>
SQL> -- create demo table
SQL> create table myTable(
2 id NUMBER(2),
3 value VARCHAR(50)
4 );
Table created.
SQL>
SQL> insert into myTable(id, value)values(1,"1234 4th St. Vancouver");
1 row created.
SQL> insert into myTable(id, value)values(2,"4 Maple Ct. New York");
1 row created.
SQL> insert into myTable(id, value)values(3,"4321 Green Blvd. London");
1 row created.
SQL> insert into myTable(id, value)values(4,"33 Third St. Toronto");
1 row created.
SQL> insert into myTable(id, value)values(5,"One First Drive. Queen");
1 row created.
SQL> insert into myTable(id, value)values(6,"1664 1/2 Springhill Ave");
1 row created.
SQL> insert into myTable(id, value)values(7,"665 Fall Ave. Linken");
1 row created.
SQL>
SQL> select * from mytable;
ID VALUE
---------- --------------------------------------------------
1 1234 4th St. Vancouver
2 4 Maple Ct. New York
3 4321 Green Blvd. London
4 33 Third St. Toronto
5 One First Drive. Queen
6 1664 1/2 Springhill Ave
7 665 Fall Ave. Linken
7 rows selected.
SQL>
SQL>
SQL> SELECT value, REGEXP_INSTR(value,"^O.e") where_it_is
2 FROM myTable
3 WHERE REGEXP_INSTR(value,"^O.e") > 0;
VALUE WHERE_IT_IS
-------------------------------------------------- -----------
One First Drive. Queen 1
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL>
SQL></source>

== Want an "e" followed by any number of other characters and then another "e"==



<source lang="sql">
SQL>
SQL> -- create demo table
SQL> create table myTable(
2 id NUMBER(2),
3 value VARCHAR(50)
4 );
Table created.
SQL>
SQL> insert into myTable(id, value)values(1,"1234 4th St. Vancouver");
1 row created.
SQL> insert into myTable(id, value)values(2,"4 Maple Ct. New York");
1 row created.
SQL> insert into myTable(id, value)values(3,"4321 Green Blvd. London");
1 row created.
SQL> insert into myTable(id, value)values(4,"33 Third St. Toronto");
1 row created.
SQL> insert into myTable(id, value)values(5,"One First Drive. Queen");
1 row created.
SQL> insert into myTable(id, value)values(6,"1664 1/2 Springhill Ave");
1 row created.
SQL> insert into myTable(id, value)values(7,"665 Fall Ave. Linken");
1 row created.
SQL>
SQL> select * from mytable;
ID VALUE
---------- --------------------------------------------------
1 1234 4th St. Vancouver
2 4 Maple Ct. New York
3 4321 Green Blvd. London
4 33 Third St. Toronto
5 One First Drive. Queen
6 1664 1/2 Springhill Ave
7 665 Fall Ave. Linken
7 rows selected.
SQL>
SQL>
SQL> SELECT value,
2 REGEXP_SUBSTR(value,"e.+e"),
3 REGEXP_INSTR(value, "e.+e") "@"
4 FROM myTable;
VALUE REGEXP_SUBSTR(VALUE,"E.+E") @
1234 4th St. Vancouver 0 4
Maple Ct. New York
e Ct. Ne 7
4321 Green Blvd. London 0
33 Third St. Toronto 0
One First Drive. Queen
e First Drive. Quee 3
1664 1/2 Springhill Ave 0
665 Fall Ave. Linken e. Linke
12
7 rows selected.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL></source>

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

Oracle PL/SQL Tutorial/Regular Expressions Functions/Meta characters - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия