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.
