Matching user input on NUMERIC fields

[nibeck]

We have a search form where the user is prompted for various fields to search on. One field is a VARCHAR, the other NUMERIC. If the fields are left blank, I want to match the ALL data.

For the VARCHAR, We simply append '%' top the end of whatever they enter, and use a 'LIKES' in the WHERE phrase. How would I handle that for a NUMERIC field? If blank, match all, If value, do exact match.

Oracle
8.1

- Mike

 

[fluteplr]

Try between.

If blank

number1 between lowestpossiblevalue and highestpossiblevalue

otherwise

number1 between enteredvalue and enteredvalue

 

[angiole]

Or, assuming col1 is alpha and col2 is numeric

select col1,col2,col3
from table1
where col1 like '%prompt_for_col1%' and
to_char(col2) like '%prompt_for_col2'

Please note that if col2 is an indexed column, the index will not be used because of the function to_char, but if it's a small table, it may not make a diff


AA :~)