INSTR Question

[request]

I have created a screen which has the stored procedure at it back end.
On the screen, there is a field called as TITLE.
The user can enter any value of TITLE and hit <SEARCH>.

e.g the TITLE is ORACLE HANDBOOK.

The user can enter the TITLE as 'RAC', 'HAND' , etc.

How can I use INSTR function with the field TITLE in my stored procedure so that no matter what the user enters, the comparison can be done.

Please let me know. Thanks.

 

[carp]

I don't know that I would use INSTR.

I would probably use:

PROCEDURE locate_title(p_title IN VARCHAR2) IS
.
.
.
SELECT <whatever>
FROM my_table
WHERE UPPER(title) LIKE '%'||UPPER(p_title)||'%';
.
.
.

This makes the query case-insensitive. Also, your users can now embed wildcards in their search - so if they wanted to find all titles with the word Oracle followed somewhere by the word Suite, they could enter something like:

Oracle%suite
which might return:

Oracle E-Business Suite Financials Handbook
Oracle E-Business Suite Financials Administration

Of course, converting the title to upper case can disable an index on that column. For performance purposes, you might want to make sure all of your titles are in upper case or else create a function-based indexed if you can.

 

[SantaMufasa]

Request,

Although you could probably use INSTR, I would opt for the &quot;LIKE&quot; comparison operator:

...WHERE upper(title) LIKE upper('%&SearchStr%')...

&quot;%&quot; is Oracle's wildcard operator.
&quot;SearchStr&quot; is the name of the SQL*Plus screen prompt that may read something similar to:
ACCEPT SearchStr PROMPT &quot;Enter (a portion of) TITLE: &quot;
Place this command before your SELECT statement.

I use the UPPER() function to ensure that no matter what a user enters, we shall compare apples to apples.

Let me know if this is what you needed.