Q
Problem solve Get help with specific problems with your technologies, process and projects.

Searching for multiple words

My Web site developer is sending me the search keyword with '+'. But it only returns the results with the order of entry. What can I do?

My Web site developer is sending me the search keyword with '+'. To get results of multiple searches I'm doing...

something like this:

select * from products
where lower(prod_name) like 
replace((lower('%'||TRIM('{keyword}')||'%')),'+','%') 

But it only returns the results with the order of entry. For example searching for 'Food Stuff' sends 'Food+Stuff' to Oracle, and this command makes it as 'food stuff'... but if I have 'Stuff related to food' as a prod name, it doesn't answer. What can I do?

There are two options. First, consider fulltext searching. This may be the most satisfactory solution, as it is the easiest and most flexible. You'll have to refer to your specific database's documentation for more details.

The second option is to split the search string into separate words, and search for them individually. This takes more coding effort but is quite practicable. So for your example, you would take the search string 'Food+Stuff' and split it into its keywords, and then search for them separately:

select * from products
 where lower(prod_name) like '%'||'food'||'%'
   and lower(prod_name) like '%'||'stuff'||'%'

Therefore this query will return a positive result when the prod_name value is 'Stuff related to food.' Notice the AND operator in the above query. This means both words must be present. You could change it to OR and then only one of the words needs to be present.

This was last published in September 2006

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close