Negating Carets : Meta characters « Regular Expressions Functions « Oracle PL/SQL Tutorial






The caret ("^") may be either an anchor or a negating marker.

We may negate the string we are looking for by placing a negating caret at the beginning of the string like this:

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,'[^gp]');

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>








18.6.Meta characters
18.6.1.Meta characters
18.6.2.The period may be substituted for any letter and still maintain a match
18.6.3.Use the caret-anchor to insist the matching start at the beginning of the string
18.6.4.Asking for a match for a capital 'F' followed by any character
18.6.5.The search string cannot be anchored at the beginning and then searched from some other position
18.6.6.Negating Carets
18.6.7.A '^' followed by something else like an 'l' (a lowercase 'L')
18.6.8.Match a string where 'i' is followed by any one character and followed by another 'i'
18.6.9.Regexp_Substr
18.6.10.'*' matches zero or more repetitions
18.6.11.'+' matches one or more repetitions
18.6.12.'?' matches exactly zero or one repetition
18.6.13.Asking to match an 'a' and zero or more 'b's:
18.6.14.A series of 'b's immediately following the 'a'
18.6.15.Matching at least one 'b'
18.6.16.Want an 'e' followed by any number of other characters and then another 'e'
18.6.17.REGEXP_SUBSTR(value,'e.*e')
18.6.18.Non-greedy '?'
18.6.19.Empty Strings and the ? Repetition Character
18.6.20.The '?' says to match zero or one time