REGEXP_INSTR(description, 'e.+e'): An 'e' followed by any number of other characters and then another 'e'
SQL> create table TestTable( 2 ID VARCHAR2(4 BYTE) NOT NULL, 3 Description VARCHAR2(30 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(description, 'e.+e'): An 'e' followed by any number of other characters and then another 'e' SQL> SQL> SELECT description, 2 REGEXP_SUBSTR(description,'e.+e'), 3 REGEXP_INSTR(description, 'e.+e') "@" 4 FROM testTable; DESCRIPTION REGEXP_SUBSTR(DESCRIPTION,'E.+ @ ------------------------------ ------------------------------ ---------- 1234 5th Street 0 1 Culloden Street en Stree 9 1234 Road 0 33 Thrid Road 0 One than another e than anothe 3 2003 Movie 0 Start With Letters ette 13 7 rows selected. SQL> SQL> drop table TestTable; Table dropped. SQL> SQL> SQL>