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")
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.
A series of "b"s immediately following the "a"
SQL>
SQL> SELECT REGEXP_SUBSTR("abbbb","ab*") FROM dual;
REGEX
-----
abbbb
Asking for a match for a capital "F" followed by any character
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>
Asking to match an "a" and zero or more "b"s:
SQL>
SQL> SELECT REGEXP_SUBSTR("a","ab*") FROM dual;
R
-
a
SQL>
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.
SQL>
SQL> SELECT REGEXP_INSTR("abc","d") FROM dual;
REGEXP_INSTR("ABC","D")
-----------------------
0
SQL>
Match a string where "i" is followed by any one character and followed by another "i"
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>
"?" matches exactly zero or one repetition
SQL>
SQL>
SQL> SELECT REGEXP_SUBSTR("Yababa dababa do","a.?a") FROM dual;
REG
---
aba
SQL>
"+" matches one or more repetitions
SQL>
SQL> SELECT REGEXP_SUBSTR("Yababa dababa do","a.+a") FROM dual;
REGEXP_SUBST
------------
ababa dababa
SQL>
"*" matches zero or more repetitions
SQL>
SQL>
SQL> SELECT REGEXP_SUBSTR("Yababa dababa do","a.*a") FROM dual;
REGEXP_SUBST
------------
ababa dababa
SQL>
Matching at least one "b"
SQL>
SQL> SELECT REGEXP_SUBSTR("a","ab+") FROM dual;
R
-
SQL>
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:
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>
Non-greedy "?"
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>
Regexp_Substr
REGEXP_SUBSTR returns part of a string.
The complete syntax of REGEXP_SUBSTR is:
REGEXP_SUBSTR(String to search, Pattern, [Position, [Occurrence, [Return-option, [Parameters]]]])
REGEXP_SUBSTR(value,"e.*e")
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>
The period may be substituted for any letter and still maintain a match
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>
The "?" says to match zero or one time
SQL>
SQL> SELECT REGEXP_INSTR("abc","d?") FROM dual;
REGEXP_INSTR("ABC","D?")
------------------------
1
SQL>
SQL> --.
SQL>
The search string cannot be anchored at the beginning and then searched from some other position
SQL>
SQL> SELECT REGEXP_INSTR("Hello","^.",2)
2 FROM dual;
REGEXP_INSTR("HELLO","^.",2)
----------------------------
0
SQL>
Use the caret-anchor to insist the matching start at the beginning of the string
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>
Want an "e" followed by any number of other characters and then another "e"
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>