REGEXP_INSTR function in where clause : REGEXP_INSTR « Regular Expressions « Oracle PL / SQL






REGEXP_INSTR function in where clause

 


SQL> create table TestTable(
  2    ID                    VARCHAR2(4 BYTE)         NOT NULL,
  3    Description           VARCHAR2(40 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> -- REGEXP_INSTR(String to search, Pattern, [Position, [Occurrence, [Return-option, [Parameters]]]])
SQL>
SQL> SELECT Description, REGEXP_INSTR(Description,'Road') where_it_is FROM TestTable
  2  WHERE REGEXP_INSTR(Description,'Road') > 0;

DESCRIPTION                              WHERE_IT_IS
---------------------------------------- -----------
1234 Road                                          6
33 Thrid Road                                     10

SQL>
SQL>
SQL>
SQL> drop table TestTable;
           
         
  








Related examples in the same category

1.regexp_instr function summary
2.Simple demo for REGEXP_INSTR
3.regexp_instr (string, pattern, position, occurence, return-option)
4.Return-option is set to 1 to indicate the end of the found pattern
5.regexp_instr (string, pattern, position, occurence, return-option, parameters): c: to match case sensitively
6.'i': to match case insensitively
7.regexp_instr (string, pattern)
8.Returns the position of the second occurrence that matches the letter 'o' starting at position 10
9.The simplest regular expression matches letters, letter for letter
10.Apecify any series of letters and find matches, just like INSTR
11.REGEXP_INSTR(description,'ee') > 0
12.REGEXP_INSTR('abc','d',1,1,1)
13.REGEXP_INSTR('abc','d?'): include the '?' repetition character
14.REGEXP_INSTR('Mississippi', 'si', 1,2,0,'i')
15.regexp_instr(comments, '[^ ]+', 1, 9)