Oracle PL/SQL Tutorial/Regular Expressions Functions/Ranges

Материал из SQL эксперт
Перейти к: навигация, поиск

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

   <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 NOT REGEXP_LIKE(value,"[0-9]");

VALUE


One First Drive. Queen SQL> SQL> SQL> drop table myTable; Table dropped. SQL> SQL></source>


Ranges (Minus Signs)

Create a range for a match using a minus sign.

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



   <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, 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></source>


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

   <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,"[^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></source>