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).
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>
[:alpha:] matches characters only.
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>
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.
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>
[:digit:] matches all numbers.
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>
[:lower:] matches lowercase characters.
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>
Matching for a non-digit
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>
[:print:] matches printable characters.
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>
[:punct:] matches punctuation.
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>
REGEXP_INSTR(value,"digit:")
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>
REGEXP_INSTR(value,"lower:")
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>
[:space:] matches spaces.
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>
The bracketed expression digit matches numbers
If we wanted to find all rows that begin with a number:
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>
[:upper:] matches uppercase characters.
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>