find () or search () function

Hi ,

has any body come across a oracle function or user defined function , which can retrieve following data .

eg : a particular column in my table contains following data

colA
------
abcdef
adfavs
cdefab
fecdab
adfbce

looking for the following out put after searching 'cde' values .
eg : something like find(colA , 'cde' )

abcdef
fecdab
cdefab
adfbce

any suggestions or script will be highly appreciated .

Is this what you are expecting?

Code:

---

SQL> insert into delme values ('abcdef');

1 row created.

SQL> insert into delme values ('adfavs');

1 row created.

SQL> insert into delme values ('cdefab');

1 row created.

SQL> insert into delme values ('fecdab');

1 row created.

SQL> insert into delme values ('adfbce');

1 row created.

SQL>
SQL> select * from delme where instr(name,'cde') > 0;

NAME
---------------------------------------------------------
cdefab
abcdef

SQL>

This contains "cde"


SQL> select * from delme where ( instr(name,'c') > 0
  2  and instr(name,'d') > 0 and instr(name,'e') > 0 );

NAME
---------------------------------------------------------
cdefab
fecdab
adfbce
abcdef

SQL>

This contains "c" and "d" and "e"

---

Sameer

use like???

thanks sameer ,
i was looking for this
now i can extend this to search words in a column and make a find()function . which could dynamically access any number of arguments to search in a single column .