Prompt For Partial Search – No Percent Sign Needed

Allow user to enter a prompt, without having to add the percent sign.  Great for searching for partial values.

Issue

If you use a Like operator in your criteria with prompts, you have to teach the user how to use the percent sign (%) to allow partial searches.  Most users learn this while using PeopleSoft pages.  But there is a way to allow the user to use a prompt field, without having to add the percent sign, and yet perform a Like operation.

Here is the issue we ran into, followed by the solution.  In criteria, if you choose the operator Like, with the left hand side being a field, the right hand side of the operation can only be a constant or prompt.  If it allowed an expression, you could sandwich the prompt between two percent signs.

Solution

The work around for Microsoft SQL Server is to make the left hand side be the following expression.  (Keep reading for the Oracle solution.) We utilized MS SQL Server’s CHARINDEX function, which is for all purposes is a FIND.  The following expression will return which character position the pattern is found in, and will return a zero if the pattern could not be found.

CHARINDEX('a', 'abcdef')

To add some more muscle around this, let’s add a prompt for the user to enter a partial string.  For our example, we’ll use the Vendor Name field (record PS_VENDOR, field NAME1).  Let’s assume this is the first prompt, which would be known as :1  (colon plus the number 1).
CHARINDEX(:1, A.NAME1)

Next, we will add the percent signs.
CHARINDEX('%' + :1 + '%', A.NAME1)

Next, we can beef up the example with Upper case functions.  That way the user can just enter the string without case concerns.
CHARINDEX(Upper('%' + :1 + '%'), Upper(A.NAME1))

Finally, we’ll finish the criteria by selecting the operator “>” (greater than), and the right hand side will be the constant value of zero (0).

Oracle Solution

For Oracle Databases, you can use INSTR.  The first two parameters are swapped though.  (And yes, I probably should have used %Concat for the + and/or || signs.  The function CHARINDEX vs. INSTR already assumes you know which database you are working in, so we might as well hardcode the concatenation symbols too.)

INSTR(Upper(A.NAME1), Upper('%' || :1 || '%'))

Final note

Be sure to let the user know (in the prompt label) that the percent sign should not be used/needed.

Also, there could be performance issues, if there is a lot of data.  The database may be unable to use an existing index when using the CHARINDEX or INSTR function.

↑ Back to top

Leave a Comment

Yay! You've decided to leave a comment. That's fantastic! Please keep in mind that comments are moderated and rel="nofollow" is in use. So, please do not use a spammy keyword or a domain as your name, or else it will be deleted. Let's have a personal and meaningful conversation instead. Thanks for dropping by! Your email address will not be published.