Asking for the presence of a 'g' or a 'p' with REGEXP_LIKE : REGEXP_LIKE « Regular Expressions Functions « Oracle PL/SQL Tutorial






The order of occurrence of [gp] or [pg] is irrelevant.

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
--------------------------------------------------
4 Maple Ct. New York
1664 1/2 Springhill Ave

SQL>
SQL>
SQL> drop table myTable;

Table dropped.

SQL>
SQL>








18.3.REGEXP_LIKE
18.3.1.REGEXP_LIKE(x, pattern [, match_option]) searchs x for the regular expression pattern.
18.3.2.Regexp_Like
18.3.3.Asking for the presence of a 'g' or a 'p'
18.3.4.Asking for the presence of a 'g' or a 'p' with REGEXP_LIKE
18.3.5.Retrieves employees whose first name starts with J or j.
18.3.6.regexp_like(comments, ' f[a-z]* ','i')
18.3.7.regexp_like(comments, '([^ ]+ ){8,}')
18.3.8.Using regexp_like(comments, '([[:alnum:]+[:punct:]]+[[:space:]]+){8,}')