Results 1 to 6 of 6

Thread: "like" keyword in oracle..

  1. #1
    Contributing Member
    Join Date
    Sep 2006
    Answers
    962

    "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


  2. #2
    Expert Member
    Join Date
    Sep 2006
    Answers
    477

    Re: "like" keyword in oracle..

    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

    [COLOR="Blue"][SIZE="2"]"If you are not living on the edge of your life, you are wasting space"[/SIZE][/COLOR]

    Someone says "Impossible is nothing". The man next him says "Let me see you licking your elbow tip!"

  3. #3
    Expert Member
    Join Date
    Jun 2006
    Answers
    410

    Re: "like" keyword in oracle..

    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
    )...


  4. #4
    Expert Member
    Join Date
    Sep 2006
    Answers
    477

    Re: "like" keyword in oracle..

    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

    Last edited by kalayama; 12-21-2006 at 04:39 AM.
    [COLOR="Blue"][SIZE="2"]"If you are not living on the edge of your life, you are wasting space"[/SIZE][/COLOR]

    Someone says "Impossible is nothing". The man next him says "Let me see you licking your elbow tip!"

  5. #5
    Expert Member
    Join Date
    Jun 2006
    Answers
    410

    Re: "like" keyword in oracle..

    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 title like input_title as input_title is not null

    author like nvl(input_author, author)
    becomes author like input_author

    and

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

    publisher like nvl(input_publisher,publisher)
    becomes publisher like publisher 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.


  6. #6
    Contributing Member
    Join Date
    Sep 2006
    Answers
    962

    Re: "like" keyword in oracle..

    Both Kalayam and James,

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

    Is other any technique to solve this problem ?

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


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact