"like" keyword in oracle..

Can anyone give a example to use the "like" keyword in select query ?

Because in my search page, user can search the book by different options..
(title, author, year, publisher,..) I am using textbox for all the fields...so user can type any one or more fields and then search for the book....Here i want to use the select query with like keyword...

-------------------
suresh

select * from TABLE where TABLE.key like '%search string%'

is the general form. If you give me your table structure and keys you need and I can write the query for you.

Cheers,
Kalayama

Suresh,

like function is meant for diff purpose. It is used to search for a specific string/character.

select * from emp where ename like 'K%';

this query returns all the records whose employee name start with 'K'

The wild card symbols can be used along with ur search string are
% - for zero or more characters
_ - single character search.

If my understanding is right like alone is not sufficient....

just try this in ur where clause

(input_title is null
or
input_title = title
)
and
(input_author is null
or
input_author = author
)...

No James. I think like is what he needs. Whe we search for anything, we must use like clause. Fore example, let use assume there is an author named James Ravid. The user just searches for James (No RAVID) don't you think the system should list this author even though a part of his name was specified?

According to
(input_author is null
or
input_author = author
), nothing will be output! (For the example above)

I think Surech is right in using like clause.
Suresh, what exactly you have in mind?

Cheers,
Kalayama

kalayama i understand his question in diff way.

In a search page, user can search the book by different options..
(title, author, year, publisher,..) and all of them are optional. User might enter them or leave them unentered.

so your query get either search string or null for each parameters...in such a case your where clause has to get generated dynamically based on the inputs given by the user. For instance user enter search string only for title and author. So ur query's where clause should have two conditions one for title and other for author.

This case the where clause would be like this

title like nvl(input_title, title)
and
author like nvl(input_author, author)
and
year like nvl(input_year,year)
and
publisher like nvl(input_publisher,publisher)

variables start with 'input_' inputs entered by the user in the search screen. And others are column names in the table.
The above query fetches all the records satisfying ur selection criterion irrespective of number of search options the used entered.

let me explain with an examle,
user entered Vinci in title and Dan in author screen and did not fill other text boxes.
Now,
title like nvl(input_title, title)
becomes [B]title like input_title[/B] as input_title is not null

author like nvl(input_author, author)
becomes [B]author like input_author[/B]

and

year like nvl(input_year,year)
becomes [B]year like year[/B] as input_year is null and this returns 'True' to all the records

publisher like nvl(input_publisher,publisher)
becomes [B]publisher like publisher[/B] and this condition is true to all the records.

so this query equivalent to

title like nvl(input_title, title) and
author like nvl(input_author, author)

Hence you achieve what you required in a Static query.

At first I though he has to match the exact term entered in the search screen so i said like is not required.

I will be very happy if this post helps Suresh. :)

Both Kalayam and James,

Thanks for your valuable reply ...... It's very useful to me.........

Is other any technique to solve this problem ?

------------
suresh