Specify any series of letters and find matches, just like INSTR : REGEXP_INSTR « Regular Expressions Functions « Oracle PL/SQL Tutorial






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,'ing') where_it_is
  2  FROM myTable
  3  WHERE REGEXP_INSTR(value,'ing') > 0;

VALUE                                              WHERE_IT_IS
-------------------------------------------------- -----------
1664 1/2 Springhill Ave                                     13

SQL>
SQL>
SQL> drop table myTable;

Table dropped.

SQL>
SQL>








18.2.REGEXP_INSTR
18.2.1.Regexp_Instr returns the location (beginning) of a pattern in a given string
18.2.2.REGEXP_INSTR(x, pattern [, start [, occurrence [, return_option [, match_option]]]]) searches for pattern in x.
18.2.3.Regexp_Instr
18.2.4.Parameters is a field that may be used to define how one wants the search to proceed:
18.2.5.Find the 's' and ignore case.
18.2.6.The simplest regular expression matches letters, letter for letter
18.2.7.SELECT REGEXP_INSTR('Two is bigger than One','One') where_it_is
18.2.8.Occurrence refers to the first, second, third, etc., occurrence of the pattern in S. The default is 1 (first).
18.2.9.Return-option returns the position of the start or end of the matched string.
18.2.10.The Return-option is set to 1 to indicate the end of the found pattern
18.2.11.Returns the position of the second occurrence that matches the regular expression s[[:alpha:]]{3} starting at position 1:
18.2.12.Returns the position of the second occurrence that matches the letter o starting at position 10 using REGEXP_INSTR()
18.2.13.Specify any series of letters and find matches, just like INSTR
18.2.14.regexp_instr(comments, '[^ ]+', 1, 9) > 0