Results 1 to 10 of 10

Thread: SQL Like query

  1. #1
    Join Date
    Dec 2004
    Posts
    9

    Unanswered: SQL Like query

    Hi All

    I have a database which has a field named 'Keyword'. In this field I have keywords by which the database will be searched. The keywords are like this (Bank, Report, NBFI). I have a ASP page which fetch data based on the following query. "Select * from Reports WHERE Keyword Like '%" & Keyword & "%'" .

    My problem is when I input 'Bank' as a search keyword. It fetched all the data but when I give 'Bank, NBFI' it doesn't show any result.

    Can any one help me here.

    Thanks

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    Check if the var 'keyword' is actually filled before executing the query. What type of datatype is the keyword column (varchar, text)?

  3. #3
    Join Date
    Sep 2003
    Location
    Germany
    Posts
    63
    When using more then one search string, I think you have to form a WHERE-clause like

    Code:
    WHERE Keyword like '%Search1%' OR Keyword like '%Search1%'
    hope that helps,
    Carsten

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if the value is 'Bank, Report, NBFI' then of course it will never be LIKE '%Bank, NBFI%'

    you will have to use your scripting language to break apart the search string ('Bank, NBFI') into its component words, and string them together with ANDs

    ... where 'Bank, Report, NBFI' like '%Bank%'
    and 'Bank, Report, NBFI' like '%NBFI%'

    you could, i suppose, just replace the comma/blanks in the search string with a percent like this --

    ... where 'Bank, Report, NBFI' like '%Bank%NBFI%'

    however, the reason you want to split them up is because if the user enters 'NFBI, Bank', then the following returns no results --

    ... where 'Bank, Report, NBFI' like '%NBFI%Bank%'

    Last edited by r937; 12-27-04 at 11:12.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I always recommended to have a Keywords table linked to its parent (whatever it may be). Then you don't need to use LIKE...But if you do at least your search criteria will not start with percent sign.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Dec 2004
    Posts
    9
    Quote Originally Posted by CarstenK
    When using more then one search string, I think you have to form a WHERE-clause like

    Code:
    WHERE Keyword like '%Search1%' OR Keyword like '%Search1%'
    hope that helps,
    Carsten
    Hi thanks for your reply but it did not solve the problem.

  7. #7
    Join Date
    Dec 2004
    Posts
    9
    Quote Originally Posted by r937
    if the value is 'Bank, Report, NBFI' then of course it will never be LIKE '%Bank, NBFI%'

    you will have to use your scripting language to break apart the search string ('Bank, NBFI') into its component words, and string them together with ANDs

    ... where 'Bank, Report, NBFI' like '%Bank%'
    and 'Bank, Report, NBFI' like '%NBFI%'

    you could, i suppose, just replace the comma/blanks in the search string with a percent like this --

    ... where 'Bank, Report, NBFI' like '%Bank%NBFI%'

    however, the reason you want to split them up is because if the user enters 'NFBI, Bank', then the following returns no results --

    ... where 'Bank, Report, NBFI' like '%NBFI%Bank%'

    Hi

    Thanks for your reply. I can split the search criteria as you have suggested. But what if a user input more than 2 or 3 keywords. I can split but how I am gonna form the 'OR" statement with this multiple search keywords.

    Aren't there any other way to seach the keyword in any form of combination like seach engines do.

    Thanks

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, split them like the search engines do

    you form the ORs using your scripting language (php, coldfusion, jsp, asp, whatever)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Dec 2004
    Posts
    9
    Quote Originally Posted by r937
    yes, split them like the search engines do

    you form the ORs using your scripting language (php, coldfusion, jsp, asp, whatever)
    As I said I can split. But don't you think if a user input more than 2 or 3 words I will have to use OR according to their keywords. How is that possible? Any example or idea. I am using ASP.


    Thanks for your quick reply.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ORs, ANDs, the semantics is up to you

    sorry, i don't do ASP
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •