Oracle PL/SQL/Regular Expressions/NOT
Содержание
Caret ("^") may be either an anchor or a negating marker
<source lang="sql">
SQL> create table TestTable(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 Description VARCHAR2(30 BYTE) 4 ) 5 /
Table created. SQL> SQL> SQL> insert into TestTable (ID, Description) values("1","1234 5th Street"); 1 row created. SQL> insert into TestTable (ID, Description) values("2","1 Culloden Street"); 1 row created. SQL> insert into TestTable (ID, Description) values("3","1234 Road"); 1 row created. SQL> insert into TestTable (ID, Description) values("4","33 Thrid Road"); 1 row created. SQL> insert into TestTable (ID, Description) values("5","One than another"); 1 row created. SQL> insert into TestTable (ID, Description) values("6","2003 Movie"); 1 row created. SQL> insert into TestTable (ID, Description) values("7","Start With Letters"); 1 row created. SQL> SQL> select * from TestTable; ID DESCRIPTION
------------------------------
1 1234 5th Street 2 1 Culloden Street 3 1234 Road 4 33 Thrid Road 5 One than another 6 2003 Movie 7 Start With Letters 7 rows selected. SQL> SQL> SQL> SQL> SQL> -- Negating Carets SQL> SQL> -- Caret ("^") may be either an anchor or a negating marker. SQL> SQL> --Negate the string by placing a negating caret at the beginning of the string SQL> SQL> SELECT description
2 FROM testTable 3 WHERE REGEXP_LIKE(description,"[^o1]");
DESCRIPTION
1234 5th Street 1 Culloden Street 1234 Road 33 Thrid Road One than another 2003 Movie Start With Letters 7 rows selected. SQL> SQL> SQL> SQL> SQL> SQL> drop table TestTable; Table dropped. SQL> SQL>
</source>
If we wanted a non-("g" or "p") followed by something else like an "l"
<source lang="sql">
SQL> create table TestTable(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 Description VARCHAR2(30 BYTE) 4 ) 5 /
Table created. SQL> SQL> SQL> insert into TestTable (ID, Description) values("1","1234 5th Street"); 1 row created. SQL> insert into TestTable (ID, Description) values("2","1 Culloden Street"); 1 row created. SQL> insert into TestTable (ID, Description) values("3","1234 Road"); 1 row created. SQL> insert into TestTable (ID, Description) values("4","33 Thrid Road"); 1 row created. SQL> insert into TestTable (ID, Description) values("5","One than another"); 1 row created. SQL> insert into TestTable (ID, Description) values("6","2003 Movie"); 1 row created. SQL> insert into TestTable (ID, Description) values("7","Start With Letters"); 1 row created. SQL> SQL> select * from TestTable; ID DESCRIPTION
------------------------------
1 1234 5th Street 2 1 Culloden Street 3 1234 Road 4 33 Thrid Road 5 One than another 6 2003 Movie 7 Start With Letters 7 rows selected. SQL> SQL> SQL> SQL> -- If we wanted a non-("g" or "p") followed by something else like an "l" SQL> SQL> SELECT description
2 FROM testTable 3 WHERE REGEXP_LIKE(description,"[^mn]t");
DESCRIPTION
1234 5th Street 1 Culloden Street One than another Start With Letters SQL> SQL> SQL> SQL> SQL> drop table TestTable; Table dropped. SQL> SQL> SQL>
</source>
Match "description" where there were no digits at all anywhere in the string
<source lang="sql">
SQL> create table TestTable(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 Description VARCHAR2(30 BYTE) 4 ) 5 /
Table created. SQL> SQL> SQL> insert into TestTable (ID, Description) values("1","1234 5th Street"); 1 row created. SQL> insert into TestTable (ID, Description) values("2","1 Culloden Street"); 1 row created. SQL> insert into TestTable (ID, Description) values("3","1234 Road"); 1 row created. SQL> insert into TestTable (ID, Description) values("4","33 Thrid Road"); 1 row created. SQL> insert into TestTable (ID, Description) values("5","One than another"); 1 row created. SQL> insert into TestTable (ID, Description) values("6","2003 Movie"); 1 row created. SQL> insert into TestTable (ID, Description) values("7","Start With Letters"); 1 row created. SQL> SQL> select * from TestTable; ID DESCRIPTION
------------------------------
1 1234 5th Street 2 1 Culloden Street 3 1234 Road 4 33 Thrid Road 5 One than another 6 2003 Movie 7 Start With Letters 7 rows selected. SQL> SQL> SQL> SQL> -- Match "description" where there were no digits at all anywhere in the string we could have used SQL> --the bracket notation, a range of numbers, and the NOT predicate. SQL> SQL> SELECT description
2 FROM testTable 3 WHERE NOT REGEXP_LIKE(description,"[0-9]");
DESCRIPTION
One than another Start With Letters SQL> SQL> drop table TestTable; Table dropped. SQL> SQL>
</source>
Use the negating caret(not): REGEXP_LIKE(description,"[^mn]")
<source lang="sql">
SQL> create table TestTable(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 Description VARCHAR2(30 BYTE) 4 ) 5 /
Table created. SQL> SQL> SQL> insert into TestTable (ID, Description) values("1","1234 5th Street"); 1 row created. SQL> insert into TestTable (ID, Description) values("2","1 Culloden Street"); 1 row created. SQL> insert into TestTable (ID, Description) values("3","1234 Road"); 1 row created. SQL> insert into TestTable (ID, Description) values("4","33 Thrid Road"); 1 row created. SQL> insert into TestTable (ID, Description) values("5","One than another"); 1 row created. SQL> insert into TestTable (ID, Description) values("6","2003 Movie"); 1 row created. SQL> insert into TestTable (ID, Description) values("7","Start With Letters"); 1 row created. SQL> SQL> select * from TestTable; ID DESCRIPTION
------------------------------
1 1234 5th Street 2 1 Culloden Street 3 1234 Road 4 33 Thrid Road 5 One than another 6 2003 Movie 7 Start With Letters 7 rows selected. SQL> SQL> SQL> -- Use the negating caret: SQL> SQL> SELECT * FROM testTable WHERE REGEXP_LIKE(description,"[^mn]"); ID DESCRIPTION
------------------------------
1 1234 5th Street 2 1 Culloden Street 3 1234 Road 4 33 Thrid Road 5 One than another 6 2003 Movie 7 Start With Letters 7 rows selected. SQL> SQL> SQL> SELECT * FROM testTable WHERE REGEXP_LIKE(description,"[mn]"); ID DESCRIPTION
------------------------------
2 1 Culloden Street 5 One than another SQL> SQL> SQL> SQL> drop table TestTable; Table dropped. SQL>
</source>
