Oracle PL/SQL Tutorial/Regular Expressions Functions/Bracketed Special Classes
Содержание
- 1 [:alnum:] matches all numbers and letters (alphanumerics).
- 2 [:alpha:] matches characters only.
- 3 Bracketed Classes
- 4 [:cntrl:] matches control characters.
- 5 [:digit:] matches all numbers.
- 6 [:lower:] matches lowercase characters.
- 7 Matching for a non-digit
- 8 [:print:] matches printable characters.
- 9 [:punct:] matches punctuation.
- 10 REGEXP_INSTR(value,"digit:")
- 11 REGEXP_INSTR(value,"lower:")
- 12 [:space:] matches spaces.
- 13 The bracketed expression digit matches numbers
- 14 [:upper:] matches uppercase characters.
[:alnum:] matches all numbers and letters (alphanumerics).
<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,"alnum:") 3 FROM myTable 4 WHERE REGEXP_INSTR(value,"alnum:") > 0;
VALUE REGEXP_INSTR(VALUE,"ALNUM:")
---------------------------------
1234 4th St. Vancouver 1 4 Maple Ct. New York 1 4321 Green Blvd. London 1 33 Third St. Toronto 1 One First Drive. Queen 1 1664 1/2 Springhill Ave 1 665 Fall Ave. Linken 1 7 rows selected. SQL> SQL> SQL> drop table myTable; Table dropped. SQL> SQL></source>
[:alpha:] matches characters only.
<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,"alpha:") 3 FROM myTable 4 WHERE REGEXP_INSTR(value,"alpha") > 0;
no rows selected SQL> SQL> SQL> drop table myTable; Table dropped. SQL> SQL></source>
Bracketed Classes
- [:digit:] matches all numbers.
- [:alnum:] matches all numbers and letters (alphanumerics).
- [:alpha:] matches characters only.
- [:lower:] matches lowercase characters.
- [:upper:] matches uppercase characters.
- [:space:] matches spaces.
- [:punct:] matches punctuation.
- [:print:] matches printable characters.
- [:cntrl:] matches control characters.
18. 8. Bracketed Special Classes 18. 8. 1. Bracketed Classes 18. 8. 2. <A href="/Tutorial/Oracle/0360__Regular-Expressions-Functions/digitmatchesallnumbers.htm">[:digit:] matches all numbers.</a> 18. 8. 3. <A href="/Tutorial/Oracle/0360__Regular-Expressions-Functions/Thebracketedexpressiondigitmatchesnumbers.htm">The bracketed expression digit matches numbers</a> 18. 8. 4. <A href="/Tutorial/Oracle/0360__Regular-Expressions-Functions/REGEXPINSTRvaluedigit.htm">REGEXP_INSTR(value,"digit:")</a> 18. 8. 5. <A href="/Tutorial/Oracle/0360__Regular-Expressions-Functions/Matchingforanondigit.htm">Matching for a non-digit</a> 18. 8. 6. <A href="/Tutorial/Oracle/0360__Regular-Expressions-Functions/alnummatchesallnumbersandlettersalphanumerics.htm">[:alnum:] matches all numbers and letters (alphanumerics).</a> 18. 8. 7. <A href="/Tutorial/Oracle/0360__Regular-Expressions-Functions/alphamatchescharactersonly.htm">[:alpha:] matches characters only.</a> 18. 8. 8. <A href="/Tutorial/Oracle/0360__Regular-Expressions-Functions/lowermatcheslowercasecharacters.htm">[:lower:] matches lowercase characters.</a> 18. 8. 9. <A href="/Tutorial/Oracle/0360__Regular-Expressions-Functions/uppermatchesuppercasecharacters.htm">[:upper:] matches uppercase characters.</a> 18. 8. 10. <A href="/Tutorial/Oracle/0360__Regular-Expressions-Functions/spacematchesspaces.htm">[:space:] matches spaces.</a> 18. 8. 11. <A href="/Tutorial/Oracle/0360__Regular-Expressions-Functions/punctmatchespunctuation.htm">[:punct:] matches punctuation.</a> 18. 8. 12. <A href="/Tutorial/Oracle/0360__Regular-Expressions-Functions/printmatchesprintablecharacters.htm">[:print:] matches printable characters.</a> 18. 8. 13. <A href="/Tutorial/Oracle/0360__Regular-Expressions-Functions/cntrlmatchescontrolcharacters.htm">[:cntrl:] matches control characters.</a> 18. 8. 14. <A href="/Tutorial/Oracle/0360__Regular-Expressions-Functions/REGEXPINSTRvaluelower.htm">REGEXP_INSTR(value,"lower:")</a>
[:cntrl:] matches control characters.
<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,"cntrl:") 3 FROM myTable 4 WHERE REGEXP_INSTR(value,"cntrl:") > 0;
no rows selected SQL> SQL> SQL> drop table myTable; Table dropped. SQL> SQL></source>
[:digit:] matches all numbers.
<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,"digit:") 3 FROM myTable 4 WHERE REGEXP_INSTR(value,"digit:") > 0;
VALUE REGEXP_INSTR(VALUE,"DIGIT:")
---------------------------------
1234 4th St. Vancouver 1 4 Maple Ct. New York 1 4321 Green Blvd. London 1 33 Third St. Toronto 1 1664 1/2 Springhill Ave 1 665 Fall Ave. Linken 1 6 rows selected. SQL> SQL> SQL> drop table myTable; Table dropped. SQL> SQL></source>
[:lower:] matches lowercase characters.
<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,"lower:") 3 FROM myTable 4 WHERE REGEXP_INSTR(value,"lower:") > 0;
VALUE REGEXP_INSTR(VALUE,"LOWER:")
---------------------------------
1234 4th St. Vancouver 7 4 Maple Ct. New York 4 4321 Green Blvd. London 7 33 Third St. Toronto 5 One First Drive. Queen 2 1664 1/2 Springhill Ave 11 665 Fall Ave. Linken 6 7 rows selected. SQL> SQL> SQL> drop table myTable; Table dropped. SQL> SQL></source>
Matching for a non-digit
<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> SELECT value
2 FROM myTable 3 WHERE NOT REGEXP_LIKE(value,"digit");
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> SQL> SQL></source>
[:print:] matches printable characters.
<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,"print:") 3 FROM myTable 4 WHERE REGEXP_INSTR(value,"print:") > 0;
VALUE REGEXP_INSTR(VALUE,"PRINT:")
---------------------------------
1234 4th St. Vancouver 1 4 Maple Ct. New York 1 4321 Green Blvd. London 1 33 Third St. Toronto 1 One First Drive. Queen 1 1664 1/2 Springhill Ave 1 665 Fall Ave. Linken 1 7 rows selected. SQL> SQL> SQL> drop table myTable; Table dropped. SQL> SQL></source>
[:punct:] matches punctuation.
<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,"punct:") 3 FROM myTable 4 WHERE REGEXP_INSTR(value,"punct:") > 0;
VALUE REGEXP_INSTR(VALUE,"PUNCT:")
---------------------------------
1234 4th St. Vancouver 12 4 Maple Ct. New York 11 4321 Green Blvd. London 16 33 Third St. Toronto 12 One First Drive. Queen 16 1664 1/2 Springhill Ave 7 665 Fall Ave. Linken 13 7 rows selected. SQL> SQL> SQL> drop table myTable; Table dropped. SQL> SQL></source>
REGEXP_INSTR(value,"digit:")
<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_INSTR(value,"digit:") = 0 4
SQL> SQL> drop table myTable; Table dropped. SQL> SQL></source>
REGEXP_INSTR(value,"lower:")
<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 REGEXP_INSTR(value,"lower:") 3 FROM myTable 4 WHERE REGEXP_INSTR(value,"lower:") > 0;
VALUE REGEXP_INSTR(VALUE,"LOWER:")
---------------------------------
1234 4th St. Vancouver 7 4 Maple Ct. New York 4 4321 Green Blvd. London 7 33 Third St. Toronto 5 One First Drive. Queen 2 1664 1/2 Springhill Ave 11 665 Fall Ave. Linken 6 7 rows selected. SQL> SQL> SQL> drop table myTable; Table dropped. SQL> SQL></source>
[:space:] matches spaces.
<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,"space:") 3 FROM myTable 4 WHERE REGEXP_INSTR(value,"space:") > 0;
VALUE REGEXP_INSTR(VALUE,"SPACE:")
---------------------------------
1234 4th St. Vancouver 5 4 Maple Ct. New York 2 4321 Green Blvd. London 5 33 Third St. Toronto 3 One First Drive. Queen 4 1664 1/2 Springhill Ave 5 665 Fall Ave. Linken 4 7 rows selected. SQL> SQL> SQL> drop table myTable; Table dropped. SQL> SQL></source>
The bracketed expression digit matches numbers
If we wanted to find all rows that begin with a number:
<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_INSTR(value,"^digit:") = 1;
VALUE
1234 4th St. Vancouver 4 Maple Ct. New York 4321 Green Blvd. London 33 Third St. Toronto 1664 1/2 Springhill Ave 665 Fall Ave. Linken 6 rows selected. SQL> SQL> SQL> drop table myTable; Table dropped. SQL> SQL> SQL></source>
[:upper:] matches uppercase characters.
<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,"upper:") 3 FROM myTable 4 WHERE REGEXP_INSTR(value,"upper:") > 0;
VALUE REGEXP_INSTR(VALUE,"UPPER:")
---------------------------------
1234 4th St. Vancouver 10 4 Maple Ct. New York 3 4321 Green Blvd. London 6 33 Third St. Toronto 4 One First Drive. Queen 1 1664 1/2 Springhill Ave 10 665 Fall Ave. Linken 5 7 rows selected. SQL> SQL> SQL> drop table myTable; Table dropped. SQL> SQL></source>
