Oracle PL/SQL Tutorial/Regular Expressions Functions/Ranges

Match value where there were no digits at all anywhere in the string with bracket notation, arrange of numbers, and the NOT predicate

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 NOT REGEXP_LIKE(value,"[0-9]");
VALUE
--------------------------------------------------
One First Drive. Queen
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL>

Ranges (Minus Signs)

Create a range for a match using a minus sign.

Ask for the letters "a" through "j" followed by an "n":

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,"[a-j]n") where_it_is
  2  FROM myTable
  3  WHERE REGEXP_INSTR(value,"[a-j]n") > 0;
VALUE                                              WHERE_IT_IS
-------------------------------------------------- -----------
1234 4th St. Vancouver                                      15
4321 Green Blvd. London                                      9
One First Drive. Queen                                      21
1664 1/2 Springhill Ave                                     13
665 Fall Ave. Linken                                        16
SQL>
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL>

REGEXP_LIKE(value,"[^0-9]")

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,"[^0-9]");
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> drop table myTable;
Table dropped.
SQL>
SQL>