PL/SQL Search Procedure

Hello,

I have created a search procedure that given a string, searches a large text file for that word. The procedure outputs the location and a section of the text around each found occurance. Now my problem is that I want the user to be able to use a wildcard with this procedure e.g. exec txtSearch('p%') will find all occurances of any words that start with 'p'.

Is there a way to do this with instr or do i have to use LIKE?
any ideas?

well I'd be curious as to how you're doing it now. I'd be inclined to use the like keyword in all cases for doing the search. I would think it'd be much faster than querying the data and then doing instr tests on it.

edit: I just re-read and see that you're reading a text file... using file_util package I assume? I still think I'd use the like keyword if it will allow you in a pl/sql statement.

open diag_list;
loop
fetch diag_list into diag_rec;
EXIT when diag_list%NOTFOUND;
loop
strPos := instr(diag_rec.DIAG_DESC,search_String,strPos + 1,1);
EXIT when strPos = 0;
strCount := strCount + 1;
insert into searchDetails values(seq_search.nextval, diag_rec.DIAG_ID, search_String,SUBSTR(diag_rec.DIAG_DESC, strPos, 30));
end loop;
end loop;

do you think i would be able to perform this with the like statement, because i actually need to find the position of the given word so I can grab a section of the text around it. And right now it's only searching a "free text" column. i.e. varchar2(200).

something that might work is to combine approaches. I can't see the definition of your cursor but maybe you could use the like statement in your cursor query to limit the data to the rows that match (you may need to use dynamic sql here) and then once you have the result set use instr to determine the position.

If I used the LIKE command, would I still be able to find the position of the word in the text. Or would I have to use INSTR as well.