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) | | |