Oracle PL/SQL Tutorial/Regular Expressions Functions/Meta characters
Содержание
- 1 A "^" followed by something else like an "l" (a lowercase "L")
- 2 A series of "b"s immediately following the "a"
- 3 Asking for a match for a capital "F" followed by any character
- 4 Asking to match an "a" and zero or more "b"s:
- 5 Empty Strings and the ? Repetition Character
- 6 Match a string where "i" is followed by any one character and followed by another "i"
- 7 "?" matches exactly zero or one repetition
- 8 "+" matches one or more repetitions
- 9 "*" matches zero or more repetitions
- 10 Matching at least one "b"
- 11 Meta characters
- 12 Negating Carets
- 13 Non-greedy "?"
- 14 Regexp_Substr
- 15 REGEXP_SUBSTR(value,"e.*e")
- 16 The period may be substituted for any letter and still maintain a match
- 17 The "?" says to match zero or one time
- 18 The search string cannot be anchored at the beginning and then searched from some other position
- 19 Use the caret-anchor to insist the matching start at the beginning of the string
- 20 Want an "e" followed by any number of other characters and then another "e"
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:
- "^" - 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.
- "$" - a dollar sign is another anchoring operator and matches only the end of a string.
- "." - the period matches anything and is called the "match any character" operator. Many would call this a "wildcard" match character.
- + matches one or more repetitions of the preceding RE
- * matches zero or more repetitions of the preceding RE
- ? matches zero or one repetition of the preceding RE
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>
