Oracle PL/SQL Tutorial/Regular Expressions Functions/Ranges — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
|
Текущая версия на 13:08, 26 мая 2010
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>