Oracle's SQL - How to get user input and search it in a query

Question

I have a database with menus and items, with a relationship between the two so that items can go on menus.

I want a select statement that can get varchar2 input from the user, and then check if the inputted value is part of the name of any items on the menus.

For example, if the user was to input 'strawberry' I would want the query to output all menus with corresponding items that have the word 'strawberry' in them. Like...

Menu                     Item
--------------------     -----------------------------
Valentine Theme          Chocolate covered strawberry       
Red Drink Theme          Strawberry Margarita
Red Drink Theme          Strawberry sensation

[How] is this possible? Here's what I've tried so far, and both have failed...

Try 1 :

select menu_name Menu, item_name Item
from menu m, item_for_menu i_m, item i
where m.menu_id=i_m.menu_id
    and i.item_id=i_m.item_id
    (and item_name = '&name_of_item'
    or item_name like '%name_of_item%');

Try 2 (after some googling):

accept item_in prompt 'What item do you want to search for? '

select menu_name Menu, item_name Item
from menu m, item_for_menu i_m, item i
where m.menu_id=i_m.menu_id
    and i.item_id=i_m.item_id
    and item_name like item_in;

undefine item_in

Timekiller · Accepted Answer · 2015-12-11 08:19:51Z

1

There are indeed better ways than SQL prompt to get input from user, but your queries are still flawed. You either don't consider the case, or forget to add wildcards. maybe there are other problems, but you didn't state how the queries failed, exactly, so I have to assume.

Try this, your modified second query:

accept item_in prompt 'What item do you want to search for? '

select menu_name Menu, item_name Item
from menu m, item_for_menu i_m, item i
where m.menu_id=i_m.menu_id
    and i.item_id=i_m.item_id
    and upper(item_name) like upper('%'||item_in||'%');

undefine item_in

If it still fails, debug. Try to execute your query without item_in at all, put the string in there directly, until you get the data you want, then replace the string with substitution variable.

answered Dec 11, 2015 at 8:19

Timekiller

3,1662 gold badges18 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Geoff · Accepted Answer · 2015-12-10 23:01:57Z

0

Are you set on trying to do all of this through SQL or are you open to other options?

For example, you could use Oracle's Java library JDBC (https://docs.oracle.com/javase/tutorial/jdbc/overview/index.html) to build an interface in Java and connect to your database that way. It would give you better control over the presentation of your data and it might be beneficial to separate users from direct access to your database by creating another tier of programming structure.

answered Dec 10, 2015 at 23:01

Geoff

1

Comments

mtfalcon31 · Accepted Answer · 2015-12-12 04:26:35Z

0

Thanks for the advice guys! Here's the working solution I ended up with:

set verify off

select 'The ' || menu_name || ' Menu contains ' || item_name Results
from menu m, item_for_menu i_m, item i
where m.menu_id=i_m.menu_id
    and i.item_id=i_m.item_id
    and item_name like '%'||'&search_for_item'||'%'
order by menu_name,item_name;

set verify on

The only thing I wish I could do now is figure out how to force the inputted value to lowercase, as well as forcing the inputted value to start with a capital letter, so that I could get more results. Still seems to work great in general though!

answered Dec 12, 2015 at 4:26

mtfalcon31

371 silver badge8 bronze badges

4 Comments

Ollie Over a year ago

Could you not use: UPPER(item_name) like UPPER('%'||'&search_for_item'||'%')

Ollie Over a year ago

Or if all your values are stored in initcap format in your database then: item_name like INITCAP('%'||'&search_for_item'||'%')

Timekiller Over a year ago

I mentioned that in my answer, but you somehow ignored it. and upper(item_name) like upper('%'||item_in||'%'); will get you all matches at once since you compare uppercased search string to uppercased input value, effectively ignoring the case. Also, please accept either of three answers, don't leave your question hanging.

mtfalcon31 Over a year ago

Sorry, still getting used to this interface. That's an awesome and perfect solution, thank you!

Collectives™ on Stack Overflow

Oracle's SQL - How to get user input and search it in a query

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related